[MDEV-7508] CONNECT can't connect to MS SQL Server Created: 2015-01-27  Updated: 2015-02-05  Resolved: 2015-02-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.15
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Geoff Montee (Inactive) Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 1
Labels: connect-engine
Environment:

CentOS 7


 Description   

I have been attempting to try out using the CONNECT storage engine to connect to MS SQL Server via MariaDB 10.0 on CentOS 7.

I did the following to set up the environment:

#Install EPEL
 
sudo rpm -ivh http://download.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
 
#Install unixODBC
 
sudo yum install unixODBC unixODBC-devel
 
#Install FreeTDS
 
sudo yum install freetds freetds-devel
 
#Install and load CONNECT
 
sudo yum install MariaDB-connect-engine
 
mysql -u root --execute="INSTALL SONAME 'ha_connect';"

Then I created the two files tds.driver.template and tds.datasource.template.

tds.driver.template

 
[FreeTDS]
 
Description     = ODBC for TDS protocol
 
Driver          = /usr/lib64/libtdsodbc.so

tds.datasource.template

 
[connect_test]
 
Driver          = FreeTDS
 
Description     = MSSQL Server
 
Trace           = No
 
Server          = 192.168.1.12
 
Database        = connect_test
 
Port            = 1433
 
TDS_Version     = 7.1

And executed:

#Install FreeTDS Driver for ODBC
 
sudo odbcinst -i -d -f tds.driver.template
 
#Install data source for ODBC
 
sudo odbcinst -i -s -l -f tds.datasource.template

Everything appears to work via ODBC's isql client:

[gmontee@localhost ~]$ isql connect_test sa 'password'
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> SELECT * FROM dbo.test_table;
 
+------------+---------------------------------------------------+
 
| a          | b                                                 |
 
+------------+---------------------------------------------------+
 
+------------+---------------------------------------------------+
 
SQLRowCount returns 0
 
SQL> INSERT INTO dbo.test_table VALUES(1, 'correct');
 
SQLRowCount returns 1
 
SQL> INSERT INTO dbo.test_table VALUES(2, 'horse');
 
SQLRowCount returns 1
 
SQL> INSERT INTO dbo.test_table VALUES(3, 'battery');
 
SQLRowCount returns 1
 
SQL> SELECT * FROM dbo.test_table;
 
+------------+---------------------------------------------------+
 
| a          | b                                                 |
 
+------------+---------------------------------------------------+
 
| 1          | correct                                           |
 
| 2          | horse                                             |
 
| 3          | battery                                           |
 
+------------+---------------------------------------------------+
 
SQLRowCount returns 3
 
3 rows fetched

However, connecting to the same data source with CONNECT doesn't work. I've tried the following different table options:

-- DSN
 
-- fields not specified
 
-- schema qualified in TABNAME
 
CREATE TABLE test_table
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
TABNAME='dbo.test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN
 
-- fields not specified
 
-- schema in DBNAME
 
CREATE TABLE test_table
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
DBNAME='dbo'
 
TABNAME='test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN
 
-- fields not specified
 
-- use default schema
 
CREATE TABLE test_table
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
TABNAME='test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN
 
-- fields specified
 
-- schema qualified in TABNAME
 
CREATE TABLE test_table (
 
	a int,
 
	b varchar(50)
 
)
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
TABNAME='dbo.test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN
 
-- fields specified
 
-- schema in DBNAME
 
CREATE TABLE test_table (
 
	a int,
 
	b varchar(50)
 
)
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
DBNAME='dbo'
 
TABNAME='test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN
 
-- fields specified
 
-- use default schema
 
CREATE TABLE test_table (
 
	a int,
 
	b varchar(50)
 
)
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
TABNAME='test_table'
 
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
 
 
 
-- DSN-less
 
-- fields specified
 
-- schema qualified in TABNAME
 
CREATE TABLE test_table (
 
	a int,
 
	b varchar(50)
 
)
 
ENGINE=CONNECT
 
TABLE_TYPE=ODBC
 
TABNAME='dbo.test_table'
 
CONNECTION='DRIVER=FreeTDS;Server=192.168.1.12;Port=1433;TDS_Version=7.1;Database=connect_test;UID=sa;PWD=password';

They all return the same vague error when attempting to connect:

ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT

I am using MS SQL Server Express 2012 which can be downloaded for free.

Considering how many components are in play here (SQL Server, FreeTDS, unixODBC, CONNECT, MariaDB), there's a good chance I'm missing an option, but I'm not sure what that would be.

 Comments   
Comment by Geoff Montee (Inactive) [ 2015-01-28 ]

I got ODBC logging to work. isql and CONNECT appear to be using different mechanisms to connect to the data source.

isql's trace log (connecting via SQLConnect.c):

[ODBC][5693][1422469905.334248][__handles.c][460]
 
                Exit:[SQL_SUCCESS]
 
                        Environment = 0x1e39750
 
[ODBC][5693][1422469905.334334][SQLAllocHandle.c][375]
 
                Entry:
 
                        Handle Type = 2
 
                        Input Handle = 0x1e39750
 
[ODBC][5693][1422469905.334373][SQLAllocHandle.c][493]
 
                Exit:[SQL_SUCCESS]
 
                        Output Handle = 0x1e3a050
 
[ODBC][5693][1422469905.334414][SQLConnect.c][3700]
 
                Entry:
 
                        Connection = 0x1e3a050
 
                        Server Name = [connect_test][length = 12 (SQL_NTS)]
 
                        User Name = [sa][length = 2 (SQL_NTS)]
 
                        Authentication = [********][length = 8 (SQL_NTS)]
 
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
 
 
 
[ODBC][5693][1422469905.366705][SQLConnect.c][4273]
 
                Exit:[SQL_SUCCESS]

And CONNECT's trace log (connecting via SQLDriverConnect.c):

[ODBC][2474][1422469178.558581][__handles.c][460]
 
                Exit:[SQL_SUCCESS]
 
                        Environment = 0x7ffdad374c00
 
[ODBC][2474][1422469178.558675][SQLAllocHandle.c][375]
 
                Entry:
 
                        Handle Type = 2
 
                        Input Handle = 0x7ffdad374c00
 
[ODBC][2474][1422469178.558714][SQLAllocHandle.c][493]
 
                Exit:[SQL_SUCCESS]
 
                        Output Handle = 0x7ffda6b74000
 
[ODBC][2474][1422469178.558755][SQLSetConnectOption.c][345]
 
                Entry:
 
                        Connection = 0x7ffda6b74000
 
                        Option = SQL_ATTR_LOGIN_TIMEOUT
 
                        Value = 15
 
[ODBC][2474][1422469178.558791][SQLSetConnectOption.c][508]
 
                Exit:[SQL_SUCCESS]
 
[ODBC][2474][1422469178.558828][SQLSetConnectOption.c][345]
 
                Entry:
 
                        Connection = 0x7ffda6b74000
 
                        Option = SQL_ATTR_ACCESS_MODE
 
                        Value = 1
 
[ODBC][2474][1422469178.558859][SQLSetConnectOption.c][508]
 
                Exit:[SQL_SUCCESS]
 
[ODBC][2474][1422469178.558900][SQLDriverConnect.c][726]
 
                Entry:
 
                        Connection = 0x7ffda6b74000
 
                        Window Hdl = 0x1
 
                        Str In = [DSN=connect_test;UID=sa;PWD=********][length = 36 (SQL_NTS)]
 
                        Str Out = 0x7ffd92800090
 
                        Str Out Max = 512
 
                        Str Out Ptr = 0x7ffdc285ea50
 
                        Completion = 0
 
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
 
 
 
                DIAG [08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist
 
 
 
                DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source
 
 
 
[ODBC][2474][1422469178.585108][SQLDriverConnect.c][1353]
 
                Exit:[SQL_ERROR]

Comment by Olivier Bertrand [ 2015-01-28 ]

The only thing I see is that isql connects via SQLConnect while CONNECT establishes the connect via SQLDriverConnect. Using SQLConnect the user name and password can be passed inside the connection string or as parameters; with SQLDriverConnect they can be passed inside the connection string or be entered interactively when prompting is enabled.

In CONNECT, SQLDriverConnect is used as a legacy to the original product that was implementing prompting but, because MariaDB does not implement prompting, this could be changed to use SQLConnect. I just did some tests and it seems to work.

What puzzles me is:

     DIAG [08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist

It could mean that something is wrong with the data source definition..

Well, this is complicated. Look on the net for this message. There are many answers such that:

I saw this error when using Pymssql for the first time. I had to turn on TCP/IP as the one of the network protocols for my local install of SQL Server.

or

Make sure you are connecting the way you think you are. The error is essentially saying it can't find the server.

It could also be something related to the used port.

Comment by Geoff Montee (Inactive) [ 2015-01-29 ]

I'm not sure what could possibly be wrong with the data source. The connect_test data source works with isql, but not with CONNECT. Both tried on the same server, as the same user account (the user running mysqld). It seems very strange for the data source to work with one ODBC client, but not another.

Does anything look wrong with the 'DSN=connect_test;UID=sa;PWD=password' connection string passed to CONNECT? It looks right to me.

Comment by Olivier Bertrand [ 2015-01-29 ]

No, the connection string seems all right. To investigate on this, I'll have to install SQL Server, make it work, and see what happens with different ways on connecting. This will take time. Be patient.

Comment by Geoff Montee (Inactive) [ 2015-01-29 ]

Oh, I'm in no rush. I haven't used unixODBC before, so even though things look right to me, I could be missing something obvious.

If you have anything in particular you want me to test, feel free to ask.

Thanks for all your work on CONNECT.

Comment by Geoff Montee (Inactive) [ 2015-01-29 ]

Hi Olivier,

I signed up for Microsoft Azure and created an instance of SQL Server in their cloud. If you don't want to create your own instance of SQL Server for testing purposes, I can give you access to this one. My subscription will be valid for the next 30 days, so feel free to use it during that time.

I'll send the connection information to the email address in your JIRA profile.

Comment by Olivier Bertrand [ 2015-01-31 ]

Hi Geoff,

Thanks for the cloud connection. I have configured a DSN under windows using the ODBC DSN administrator.
Two remarks:

  1. There is no provision to specify a port number.
  2. The database I specified 'connect_test' was not accepted.
    Any way the connection was successfully established during the configuration process.

Then I defined a CONNECT table by:

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineSQLServer;UID=connect_test;PWD=Password1' CATFUNC=tables;

And when I did:

select * from test_table;

The connection was successful and the reply:

Table_Cat Table_Schema Table_Name Table_Type Remark
master dbo sysdac_history_internal TABLE <null>
master dbo sysdac_instances_internal TABLE <null>
master dbo server_quotas VIEW <null>
master dbo slo_assignment_history VIEW <null>
master dbo slo_database_objectives VIEW <null>
master dbo slo_dimension_settings VIEW <null>
master dbo slo_objective_setting_selections VIEW <null>
master dbo slo_service_dimensions VIEW <null>
master dbo slo_service_objectives VIEW <null>
master dbo sysdac_instances VIEW <null>
master INFORMATION_SCHEMA CHECK_CONSTRAINTS VIEW <null>
master INFORMATION_SCHEMA COLUMN_DOMAIN_USAGE VIEW <null>
master INFORMATION_SCHEMA COLUMN_PRIVILEGES VIEW <null>
master INFORMATION_SCHEMA COLUMNS VIEW <null>
master INFORMATION_SCHEMA CONSTRAINT_COLUMN_USAGE VIEW <null>
master INFORMATION_SCHEMA CONSTRAINT_TABLE_USAGE VIEW <null>
master INFORMATION_SCHEMA DOMAIN_CONSTRAINTS VIEW <null>
master INFORMATION_SCHEMA DOMAINS VIEW <null>
master INFORMATION_SCHEMA KEY_COLUMN_USAGE VIEW <null>
master INFORMATION_SCHEMA PARAMETERS VIEW <null>
master INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS VIEW <null>
master INFORMATION_SCHEMA ROUTINE_COLUMNS VIEW <null>
master INFORMATION_SCHEMA ROUTINES VIEW <null>
master INFORMATION_SCHEMA SCHEMATA VIEW <null>
master INFORMATION_SCHEMA TABLE_CONSTRAINTS VIEW <null>
master INFORMATION_SCHEMA TABLE_PRIVILEGES VIEW <null>
master INFORMATION_SCHEMA TABLES VIEW <null>
master INFORMATION_SCHEMA VIEW_COLUMN_USAGE VIEW <null>
master INFORMATION_SCHEMA VIEW_TABLE_USAGE VIEW <null>
master INFORMATION_SCHEMA VIEWS VIEW <null>
master sys all_columns VIEW <null>
master sys all_objects VIEW <null>
master sys all_parameters VIEW <null>
master sys all_sql_modules VIEW <null>
master sys all_views VIEW <null>
master sys assemblies VIEW <null>
master sys assembly_types VIEW <null>
master sys audits VIEW <null>
master sys bandwidth_usage VIEW <null>
master sys check_constraints VIEW <null>
master sys column_type_usages VIEW <null>
master sys columns VIEW <null>
master sys computed_columns VIEW <null>
master sys database_audit_specification_details VIEW <null>
master sys database_audit_specifications VIEW <null>
master sys database_connection_stats VIEW <null>
master sys database_firewall_rules VIEW <null>
master sys database_permissions VIEW <null>
master sys database_principals VIEW <null>
master sys database_query_store_options VIEW <null>
master sys database_role_members VIEW <null>
master sys database_usage VIEW <null>
master sys databases VIEW <null>
master sys default_constraints VIEW <null>
master sys dm_continuous_copy_status VIEW <null>
master sys dm_database_copies VIEW <null>
master sys dm_db_index_usage_stats VIEW <null>
master sys dm_db_missing_index_details VIEW <null>
master sys dm_db_missing_index_group_stats VIEW <null>
master sys dm_db_missing_index_groups VIEW <null>
master sys dm_db_objects_impacted_on_version_change VIEW <null>
master sys dm_db_partition_stats VIEW <null>
master sys dm_db_resource_stats VIEW <null>
master sys dm_db_wait_stats VIEW <null>
master sys dm_exec_cached_plans VIEW <null>
master sys dm_exec_connections VIEW <null>
master sys dm_exec_procedure_stats VIEW <null>
master sys dm_exec_query_memory_grants VIEW <null>
master sys dm_exec_query_stats VIEW <null>
master sys dm_exec_requests VIEW <null>
master sys dm_exec_sessions VIEW <null>
master sys dm_exec_trigger_stats VIEW <null>
master sys dm_federation_operation_error_members VIEW <null>
master sys dm_federation_operation_errors VIEW <null>
master sys dm_federation_operation_members VIEW <null>
master sys dm_federation_operations VIEW <null>
master sys dm_operation_status VIEW <null>
master sys dm_tran_active_transactions VIEW <null>
master sys dm_tran_database_transactions VIEW <null>
master sys dm_tran_locks VIEW <null>
master sys dm_tran_session_transactions VIEW <null>
master sys dm_xe_database_session_event_actions VIEW <null>
master sys dm_xe_database_session_events VIEW <null>
master sys dm_xe_database_session_object_columns VIEW <null>
master sys dm_xe_database_session_targets VIEW <null>
master sys dm_xe_database_sessions VIEW <null>
master sys event_log VIEW <null>
master sys event_notification_event_types VIEW <null>
master sys event_notifications VIEW <null>
master sys event_session_actions VIEW <null>
master sys event_session_events VIEW <null>
master sys event_session_fields VIEW <null>
master sys event_session_targets VIEW <null>
master sys event_sessions VIEW <null>
master sys events VIEW <null>
master sys federated_table_columns VIEW <null>
master sys federation_distribution_history VIEW <null>
master sys federation_distributions VIEW <null>
master sys federation_history VIEW <null>
master sys federation_member_distribution_history VIEW <null>
master sys federation_member_distributions VIEW <null>
master sys federation_member_history VIEW <null>
master sys federation_members VIEW <null>
master sys federations VIEW <null>
master sys firewall_rules VIEW <null>
master sys foreign_key_columns VIEW <null>
master sys foreign_keys VIEW <null>
master sys identity_columns VIEW <null>
master sys index_columns VIEW <null>
master sys indexes VIEW <null>
master sys key_constraints VIEW <null>
master sys numbered_procedure_parameters VIEW <null>
master sys numbered_procedures VIEW <null>
master sys objects VIEW <null>
master sys parameter_type_usages VIEW <null>
master sys parameters VIEW <null>
master sys plan_guides VIEW <null>
master sys procedures VIEW <null>
master sys query_context_settings VIEW <null>
master sys query_store_plan VIEW <null>
master sys query_store_query VIEW <null>
master sys query_store_query_text VIEW <null>
master sys query_store_runtime_stats VIEW <null>
master sys query_store_runtime_stats_interval VIEW <null>
master sys resource_stats VIEW <null>
master sys resource_usage VIEW <null>
master sys schemas VIEW <null>
master sys selective_xml_index_namespaces VIEW <null>
master sys selective_xml_index_paths VIEW <null>
master sys spatial_index_tessellations VIEW <null>
master sys spatial_indexes VIEW <null>
master sys spatial_reference_systems VIEW <null>
master sys sql_logins VIEW <null>
master sys sql_modules VIEW <null>
master sys stats VIEW <null>
master sys stats_columns VIEW <null>
master sys synonyms VIEW <null>
master sys syscharsets VIEW <null>
master sys syscolumns VIEW <null>
master sys syscursorrefs VIEW <null>
master sys syscursors VIEW <null>
master sys sysdatabases VIEW <null>
master sys syslanguages VIEW <null>
master sys sysobjects VIEW <null>
master sys sysreferences VIEW <null>
master sys system_columns VIEW <null>
master sys system_objects VIEW <null>
master sys system_parameters VIEW <null>
master sys system_sql_modules VIEW <null>
master sys system_views VIEW <null>
master sys systypes VIEW <null>
master sys sysusers VIEW <null>
master sys table_types VIEW <null>
master sys tables VIEW <null>
master sys trigger_event_types VIEW <null>
master sys trigger_events VIEW <null>
master sys triggers VIEW <null>
master sys types VIEW <null>
master sys user_token VIEW <null>
master sys views VIEW <null>
master sys xml_indexes VIEW <null>
master sys xml_schema_collections VIEW <null>

Apparently the connect_test database and and connect_test database do not exist.
Besides in your examples, the database is dbo. It does not not exist either.

Comment by Geoff Montee (Inactive) [ 2015-01-31 ]

Hi Olivier,

I have configured a DSN under windows using the ODBC DSN administrator.

You tried on Windows? My problems occurred on CentOS 7 with unixODBC and FreeTDS.

There is no provision to specify a port number.

With unixODBC and FreeTDS, you can specify a port number in the DSN definition. See here. I'm not sure why Windows wouldn't let you do that.

The database I specified 'connect_test' was not accepted.

I'm not sure why it would not be accepted. The database exists, and I can connect to it with unixODBC and FreeTDS using the isql client. However, I cannot connect with MariaDB and the CONNECT engine.

Apparently the connect_test database and and connect_test database do not exist. Besides in your examples, the database is dbo. It does not not exist either.

Actually, in my examples, connect_test is the database, and dbo is the schema (in MS SQL Server, these are different things). In some of the test examples, I put the schema name (dbo) in DBNAME because that's what the documentation says to do at the bottom of this page. In my examples, the actual database name is in the DSN definition in tds.datasource.template which gets added to odbc.ini after calling odbcinst.

Based on what you said and the output you received, it looks like you connected to the "default" database (which is the master system database) rather than the connect_test database.

Anyway, if you are using Windows, and you still couldn't connect to the connect_test database with CONNECT, but you could connect to the "default" database, maybe there's something strange going on with regards to how CONNECT uses the ODBC API to select a non-default database that doesn't quite work with MS SQL Server?

Thanks for looking at this issue.

Comment by Olivier Bertrand [ 2015-02-02 ]

I have been heavily working on this issue and finally managed to read your table, as well under Linux (ubuntu) and Windows.

First of all, to make things clear, an ODBC table depends on three components:

Catalog: (was qualifier) is where the database is. For instance many servers store their database data in a file, the Catalog is what allows retrieving the file containing the data(base).
Schema: (was database). A catalog database can be logically separated in table sets. For ODBC this is now call the schema and this corresponds to MySQL databases.
Table: Anyone knows what a table is.

The problem with SQL Server is that what they call "Database" is in fact the ODBC "Catalog".
The problem with CONNECT is that some option names had been chosen before these change of names:

Catalog (or Qualifier) is used to specify the Catalog name (in the OPTION_LIST)
DBname (or Schema in the OPTION_LIST) is used to specify the Schema name.
TABname is used to specify the table name.

For instance, in your sample test, the table name is 'test_table' in the 'dbo' schema belonging to the 'connect_test' catalog.

On Linux (ubuntu)

I followed your script to install FreeTDS, and used odbcinst to install the driver and the 'connect_test_azure' data source. Using isql all was alright and I could read the test_table successfully.

[connect_test_azure]
 
Driver = FreeTDS
 
Description = MSSQL Server
 
Trace = No
 
Server = iwo9jlpffq.database.windows.net
 
Database = connect_test
 
Port = 1433
 
TDS_Version = 7.1

However, nothing worked on MariaDB with a CONNECT table. I took me quite a while to understand what was happening.

The problem on my ubuntu machine, was that there was two odbc.ini files! One in /etc/odbc.ini and the other one in /etc/unixODBC/odbc.ini.

The odbcinst program made the data source description in /etc/odbc.ini and this is the one used by isql.

However, I don't know why, but when unixODBC is used from MariaDB it uses /etc/unixODBC/odbc.ini. So I had to manually copy the data source description in this file.
But it was not enough. In this configuration, unixODBC does not recognize the Driver just given by a name. So I modified it to become:

[connect_test_azure]
 
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
 
Description = MSSQL Server
 
Trace = No
 
Server = iwo9jlpffq.database.windows.net
 
Database = connect_test
 
Port = 1433
 
TDS_Version = 7.1

After this all worked smoothly:

buggynours@UBUNTU:~/repos/10.0.14/10.0-connect$ /usr/local/mysql/bin/mysql
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 10.0.15-MariaDB-log Source distribution
 
 
 
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> use test
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
 
 
MariaDB [test]> CREATE or replace table test_table ENGINE=CONNECT CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1;' TABLE_TYPE=ODBC TABNAME='dbo.test_table';
 
Query OK, 0 rows affected (3.03 sec)
 
 
 
MariaDB [test]> select * from test_table;
 
+---+---------+
 
| a | b       |
 
+---+---------+
 
| 1 | correct |
 
| 2 | horse   |
 
| 3 | battery |
 
+---+---------+
 
3 rows in set (2.99 sec)

On Windows

There is surely a bug or limitation in the provided Data Source configuration utility, as it was impossible to specify the default "Database" (The Catalog) Besides I had the same problem accessing your table from ODBCTE32.EXE (the Windows equivalent to isql) than with CONNECT.

However, knowing that it was th Catalog name to be specified, the Catalog information can be provided when creating the table. For instance:

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='test_table' DBNAME='dbo'
 
OPTION_LIST='Catalog=connect_test'
 
CONNECTION='DSN=ConnectEngineSQLServer;UID=connect_test;PWD=Password1';

or:

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='dbo.test_table'
 
OPTION_LIST='Catalog=connect_test'
 
CONNECTION='DSN=ConnectEngineSQLServer;UID=connect_test;PWD=Password1';

or even:

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='connect_test.dbo.test_table'
 
CONNECTION='DSN=ConnectEngineSQLServer;UID=connect_test;PWD=Password1';

The catalog can also be specified in the connection string:

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='dbo.test_table'
 
CONNECTION='DSN=ConnectEngineSQLServer;DATABASE=connect_test;UID=connect_test;PWD=Password1';

All these worked:

select * from test_table;

Returns:

a b
1 correct
2 horse
3 battery

Please check whether this information can help you solve your problem so I can close this case.

Comment by Geoff Montee (Inactive) [ 2015-02-02 ]

Thanks for working on this. It's still not working on CentOS 7 for some reason.

Catalog (or Qualifier) is used to specify the Catalog name (in the OPTION_LIST)
DBname (or Schema in the OPTION_LIST) is used to specify the Schema name.
TABname is used to specify the table name

Hmm. When using unixODBC with MS SQL Server, it sounds like this Catalog option is duplicating the Database option that appears in odbc.ini. It sounds like this option may be required for Windows ODBC, but it probably shouldn't be necessary for unixODBC, right? It looks like you managed to connect without it on Ubuntu, so I guess not.

In this configuration, unixODBC does not recognize the Driver just given by a name.

I modified my datasource file like to have a hard-coded path to the driver's shared library:

tds_azure.datasource.template

 
[connect_test_azure]
 
Driver          = /usr/lib64/libtdsodbc.so
 
Description     = MSSQL Server
 
Trace           = No
 
Server          = iwo9jlpffq.database.windows.net
 
Database        = connect_test
 
Port            = 1433
 
TDS_Version     = 7.1

Then ran:

sudo odbcinst -i -s -l -f tds_azure.datasource.template

isql was still happy with this.

[gmontee@localhost ~]$ isql connect_test_azure connect_test 'Password1'
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> SELECT * FROM dbo.test_table;
 
+------------+---------------------------------------------------+
 
| a          | b                                                 |
 
+------------+---------------------------------------------------+
 
| 1          | correct                                           |
 
| 2          | horse                                             |
 
| 3          | battery                                           |
 
+------------+---------------------------------------------------+
 
SQLRowCount returns 3
 
3 rows fetched

CONNECT still would not accept it, even after adding Catalog to OPTION_LIST.

MariaDB [tmp]> CREATE TABLE test_table
 
    -> ENGINE=CONNECT
 
    -> TABLE_TYPE=ODBC
 
    -> DBNAME='dbo'
 
    -> TABNAME='test_table'
 
    -> OPTION_LIST='Catalog=connect_test'
 
    -> CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1';
 
ERROR 1105 (HY000): [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

However, I don't know why, but when unixODBC is used from MariaDB it uses /etc/unixODBC/odbc.ini. So I had to manually copy the data source description in this file.

This directory doesn't exist on CentOS 7. However, creating it and putting the configuration file in it doesn't seem to change things. Adding Catalog to OPTION_LIST doesn't change things either.

[gmontee@localhost ~]$ sudo mkdir -p /etc/unixODBC
 
[gmontee@localhost ~]$ sudo cp /etc/odbc.ini /etc/unixODBC/
 
[gmontee@localhost ~]$ mysql -u root tmp
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 15
 
Server version: 10.0.15-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [tmp]> CREATE TABLE test_table
 
    -> ENGINE=CONNECT
 
    -> TABLE_TYPE=ODBC
 
    -> DBNAME='dbo'
 
    -> TABNAME='test_table'
 
    -> CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1';
 
ERROR 1105 (HY000): [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
 
MariaDB [tmp]> CREATE TABLE test_table
 
    -> ENGINE=CONNECT
 
    -> TABLE_TYPE=ODBC
 
    -> DBNAME='dbo'
 
    -> TABNAME='test_table'
 
    -> OPTION_LIST='Catalog=connect_test'
 
    -> CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1';
 
ERROR 1105 (HY000): [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Maybe there's an incompatibility of some kind between CONNECT and the versions of ODBC or FreeTDS included in CentOS 7? What versions did you use on Ubuntu? Also, based on your driver path (/usr/lib/i386-linux-gnu/odbc/libtdsodbc.so), it looks like you are using 32-bit? I'm using 64-bit.

[gmontee@localhost ~]$ sudo yum list unixODBC*
 
Loaded plugins: fastestmirror, langpacks
 
Loading mirror speeds from cached hostfile
 
 * base: distro.ibiblio.org
 
 * epel: fedora.mirrors.pair.com
 
 * extras: mirror.net.cen.ct.gov
 
 * updates: mirrors.lga7.us.voxel.net
 
Installed Packages
 
unixODBC.x86_64                                                                                                                        2.3.1-10.el7                                                                                                                  @anaconda
 
unixODBC-devel.x86_64                                                                                                                  2.3.1-10.el7                                                                                                                  @anaconda
 
Available Packages
 
unixODBC.i686                                                                                                                          2.3.1-10.el7                                                                                                                  base     
unixODBC-devel.i686                                                                                                                    2.3.1-10.el7                                                                                                                  base     
[gmontee@localhost ~]$ sudo yum list freetds*
 
Loaded plugins: fastestmirror, langpacks
 
Loading mirror speeds from cached hostfile
 
 * base: mirror.solarvps.com
 
 * epel: mirror.symnds.com
 
 * extras: mirror.net.cen.ct.gov
 
 * updates: mirror.symnds.com
 
Installed Packages
 
freetds.x86_64                                                                                                                     0.91-12.git0a42888.el7                                                                                                                @epel
 
freetds-devel.x86_64                                                                                                               0.91-12.git0a42888.el7                                                                                                                @epel
 
Available Packages
 
freetds-doc.noarch                                                                                                                 0.91-12.git0a42888.el7                                                                                                                epel

Comment by Olivier Bertrand [ 2015-02-03 ]

It sounds like this option may be required for Windows ODBC, but it probably shouldn't be necessary for unixODBC, right? It looks like you managed to connect without it on Ubuntu, so I guess not.

It was required on Windows because it was not possible to set it in the Data Source definition. On Linux it was part of the definition placed in odbc.ini.

In this configuration, unixODBC does not recognize the Driver just given by a name.

This was probably because I had copied the data source definition from /etc/odbc.ini to /etc/unixODBC/odbc.ini but I did not do the same for odbcinst.ini.

Now, setting the whole thing to work turned out to be sometimes very difficult and I spent several hours sometimes to find small errors or bad settings that were making the whole thing to fail. This doesn't prove that you can't succeed to read your table with CONNECT, but there are very many things on Linux that must be fixed. For instance, setting the permission of the directory on wich the data file is so it can be accessed by MariaDB.

Comment by Geoff Montee (Inactive) [ 2015-02-03 ]

For instance, setting the permission of the directory on wich the data file is so it can be accessed by MariaDB.

I considered that particular case as well. However, the mysql user can also query via isql with no issues.

[gmontee@localhost ~]$ sudo -u mysql isql connect_test_azure connect_test 'Password1'
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> SELECT * FROM dbo.test_table;
 
+------------+---------------------------------------------------+
 
| a          | b                                                 |
 
+------------+---------------------------------------------------+
 
| 1          | correct                                           |
 
| 2          | horse                                             |
 
| 3          | battery                                           |
 
+------------+---------------------------------------------------+
 
SQLRowCount returns 3
 
3 rows fetched

I also tried cutting out the configuration files entirely, by using a DSN-less configuration. This did not work with CONNECT either.

MariaDB [tmp]> CREATE TABLE test_table
 
    -> ENGINE=CONNECT
 
    -> TABLE_TYPE=ODBC
 
    -> DBNAME='dbo'
 
    -> TABNAME='test_table'
 
    -> CONNECTION='DRIVER=/usr/lib64/libtdsodbc.so;Server=iwo9jlpffq.database.windows.net;Port=1433;TDS_Version=7.1;Database=connect_test;UID=connect_test;PWD=Password1';
 
ERROR 1105 (HY000): [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Interestingly, going back to the top of the thread...

The only thing I see is that isql connects via SQLConnect while CONNECT establishes the connect via SQLDriverConnect.

isql has an option -k to use SQLDriverConnect instead of SQLConnect. This also fails!

[gmontee@localhost ~]$ isql -k connect_test_azure connect_test 'Password1'
 
[ISQL]ERROR: Could not SQLDriverConnect

It looks like the versions of unixODBC or FreeTDS on CentOS 7 may have a bug in the implementation of SQLDriverConnect that prevents connecting to MS SQL Server.

I'll probably pass this information to the FreeTDS mailing list next. So I have a good idea of what environment actually works, can you please tell me what version of Ubuntu you used to successfully connect?

Comment by Olivier Bertrand [ 2015-02-03 ]

I am using ubuntu 14.04 LTS (32 bits)

By the way, I added to CONNECT the possibility use use SQLConnect or SQLDriverConnect. However, this will be available only in the next release.

Comment by Geoff Montee (Inactive) [ 2015-02-03 ]

I am using ubuntu 14.04 LTS (32 bits)

Great, thanks! I'll look into the differences and see if the FreeTDS developers have any insight into the problem.

By the way, I added to CONNECT the possibility use use SQLConnect or SQLDriverConnect. However, this will be available only in the next release.

Awesome! Thanks for all your work on CONNECT.

Comment by Geoff Montee (Inactive) [ 2015-02-03 ]

It worked on 64-bit Ubuntu 14.04 on my first try.

Set up ODBC stuff:

sudo apt-get install unixodbc
 
sudo apt-get install tdsodbc
 
cat > tds.driver.template <<EOM
 
[FreeTDS]
 
Description     = ODBC for TDS protocol
 
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
 
EOM
 
cat > tds.datasource.template <<EOM
 
[connect_test]
 
Driver          = FreeTDS
 
Description     = MSSQL Server
 
Trace           = No
 
Server          = iwo9jlpffq.database.windows.net
 
Database        = connect_test
 
Port            = 1433
 
TDS_Version     = 7.1
 
EOM
 
sudo odbcinst -i -d -f tds.driver.template
 
sudo odbcinst -i -s -l -f tds.datasource.template

Add MariaDB repo:

sudo apt-get install software-properties-common
 
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
 
sudo add-apt-repository 'deb http://ftp.utexas.edu/mariadb/repo/10.0/ubuntu trusty main'
 
sudo apt-get update

Install MariaDB and connect:

sudo apt-get update
 
sudo apt-get install mariadb-server-10.0 mariadb-client-10.0
 
sudo apt-get install mariadb-connect-engine-10.0
 
mysql -u root --execute="INSTALL SONAME 'ha_connect';"
 
mysql -u root --execute="CREATE DATABASE tmp;"

Now test it out:

gmontee@gmontee-VirtualBox:~$ mysql -u root tmp
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 44
 
Server version: 10.0.16-MariaDB-1~trusty mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [tmp]> CREATE TABLE test_table
 
    -> ENGINE=CONNECT
 
    -> TABLE_TYPE=ODBC
 
    -> TABNAME='dbo.test_table'
 
    -> CONNECTION='DSN=connect_test;UID=connect_test;PWD=Password1';
 
Query OK, 0 rows affected (2.24 sec)
 
 
 
MariaDB [tmp]> SELECT * FROM test_table;
 
+---+---------+
 
| a | b       |
 
+---+---------+
 
| 1 | correct |
 
| 2 | horse   |
 
| 3 | battery |
 
+---+---------+
 
3 rows in set (0.54 sec)

The issues I have been having seem specific to the packages included in CentOS 7.

Comment by Geoff Montee (Inactive) [ 2015-02-05 ]

I talked to the FreeTDS developer here, but FreeTDS and unixODBC turned out to be irrelevant to this problem.

It turns out that SELinux in CentOS 7 doesn't allow /usr/sbin/mysqld to make outgoing TCP connections. I set SELinux to permissive mode, and now everything works. /var/log/messages shows:

Feb  4 18:56:17 localhost setroubleshoot: SELinux is preventing /usr/sbin/mysqld from name_connect access on the tcp_socket . For complete SELinux messages. run sealert -l d7f77d24-7340-40f3-ae75-a41cc1c4f454
 
Feb  4 18:56:17 localhost python: SELinux is preventing /usr/sbin/mysqld from name_connect access on the tcp_socket .
 
 
 
*****  Plugin catchall_boolean (89.3 confidence) suggests   ******************
 
 
 
If you want to allow mysql to connect any
 
Then you must tell SELinux about this by enabling the 'mysql_connect_any' boolean.
 
You can read 'None' man page for more details.
 
Do
 
setsebool -P mysql_connect_any 1
 
 
 
*****  Plugin catchall (11.6 confidence) suggests   **************************
 
 
 
If you believe that mysqld should be allowed name_connect access on the  tcp_socket by default.
 
Then you should report this as a bug.
 
You can generate a local policy module to allow this access.
 
Do
 
allow this access for now by executing:
 
# grep mysqld /var/log/audit/audit.log | audit2allow -M mypol
 
# semodule -i mypol.pp

/var/log/audit/audit.log shows:

type=AVC msg=audit(1423094175.109:433): avc:  denied  { name_connect } for  pid=3193 comm="mysqld" dest=1433 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:mssql_port_t:s0 tclass=tcp_socket
 
type=SYSCALL msg=audit(1423094175.109:433): arch=c000003e syscall=42 success=no exit=-115 a0=3a a1=7f9de7233400 a2=10 a3=7f9de72331a0 items=0 ppid=1716 pid=3193 auid=4294967295 uid=991 gid=989 euid=991 suid=991 fsuid=991 egid=989 sgid=989 fsgid=989 tty=(none) ses=4294967295 comm="mysqld" exe="/usr/sbin/mysqld" subj=system_u:system_r:mysqld_t:s0 key=(null)

We may want to document this in CONNECT's ODBC documentation.

Comment by Olivier Bertrand [ 2015-02-05 ]

Indeed, this must be documented, along some with other connection issues.

Generated at Thu Feb 08 07:20:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.