Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.4
-
None
-
None
-
Debian 7.1 and CentOS 6.4
Description
I am trying to connect to SQL Server 2008 R2 using the connect engine and running mysql from linux
I have tried on two different platforms to make this work
Debian 7.1 64-bit and CentOS 6.4 64 bit.
############
Debian 7.1 details
Linux db149 3.2.0-4-amd64 #1 SMP Debian 3.2.46-1+deb7u1 x86_64 GNU/Linux
relevant packages installed:
ii libmariadbclient18 10.0.4+maria-1~wheezy amd64 MariaDB database client library
|
ii libmysqlclient18 10.0.4+maria-1~wheezy amd64 Virtual package to satisfy external depends
|
ii mariadb-client-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database client binaries
|
ii mariadb-client-core-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database core client binaries
|
ii mariadb-common 10.0.4+maria-1~wheezy all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
|
iU mariadb-connect-engine-10.0 10.0.4+maria-1~wheezy all Connect storage engine for MariaDB
|
iF mariadb-server-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database server binaries
|
ii mariadb-server-core-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database core server files
|
ii libodbc1:amd64 2.2.14p2-5 amd64 ODBC library for Unix
|
ii odbcinst 2.2.14p2-5 amd64 Helper program for accessing odbc ini files
|
ii odbcinst1debian2:amd64 2.2.14p2-5 amd64 Support library for accessing odbc ini files
|
ii tdsodbc:amd64 0.91-2 amd64 ODBC driver for connecting to MS SQL and Sybase SQL servers
|
ii unixodbc 2.2.14p2-5 amd64 Basic ODBC tools
|
ii unixodbc-dev 2.2.14p2-5 amd64 ODBC libraries for UNIX (development files)
|
ii freetds-bin 0.91-2+deb7u1 amd64 FreeTDS command-line utilities
|
ii freetds-common 0.91-2 all configuration files for FreeTDS SQL client libraries
|
cat /etc/odbc.ini
|
[MSSQLTestServer]
|
Driver = FreeTDS
|
Server = 192.168.1.20
|
Port = 1433
|
cat /etc/odbcinst.ini
|
[FreeTDS]
|
Description = TDS driver (Sybase/MS SQL)
|
Driver = libtdsodbc.so
|
Setup = libtdsS.so
|
CPTimeout =
|
CPReuse =
|
Trace = yes
|
TraceFile = /tmp/sql.log
|
ForceTrace = Yes
|
Database = master
|
Proof that odbc is working from linux to Sql server via the command line. Note that credentials have to be supplied. I have sanitized the username and password.
isql -v MSSQLTestServer user password
|
+---------------------------------------+
|
| Connected! |
|
| |
|
| sql-statement |
|
| help [tablename] |
|
| quit |
|
| |
|
+---------------------------------------+
|
SQL> select * from connect_table;
|
+------------+
|
| id |
|
+------------+
|
| 1001 |
|
| 1002 |
|
+------------+
|
SQLRowCount returns 2
|
2 rows fetched
|
Attempt to create connect table from within maria
(192.168.1.20:test:5)$ CREATE TABLE connect_table (
|
-> id int(10) NOT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer' table_type=odbc block_size=10 tabname='connect_table';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
(192.168.1.20:test:6)$ select * from connect_table;
|
ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT
|
(192.168.1.20:test:7)$
|
##################################
CentOs 6.4 details
CentOS release 6.4 (Final)
Linux maria1 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
MariaDB-common-10.0.4-1.x86_64
|
MariaDB-connect-engine-10.0.4-1.x86_64
|
MariaDB-compat-10.0.4-1.x86_64
|
MariaDB-server-10.0.4-1.x86_64
|
MariaDB-client-10.0.4-1.x86_64
|
freetds-0.64-1.el6.rf.x86_64
|
unixODBC-2.2.14-12.el6_3.x86_64
|
cat /etc/odbc.ini
|
[MSSQLTestServer]
|
Driver = ODBC Driver 11 for SQL Server
|
Server = 192.168.1.20
|
Port = 1433
|
cat /etc/odbcinst.ini
|
[ODBC Driver 11 for SQL Server]
|
Description=Microsoft ODBC Driver 11 for SQL Server
|
Driver=/opt/lib/libmsodbcsql-11.0.so.2270.0
|
Threading=1
|
UsageCount=1
|
Ok on CentOS I first tried to setup Integrated Authentication from Linux to Microsoft SQL Server and I was successful.
I followed this instruction http://msdn.microsoft.com/en-us/library/hh568451.aspx
Here is the proof that it works.
./sqlcmd -E -S SVDWDBCLN1.sellingsource.local -d master
|
1> select * from connect_table;
|
2> go
|
id
|
-----------
|
1001
|
1002
|
|
|
(2 rows affected)
|
Now keep in mind that in prior test on debian i used freetds as my SQL Server driver.
Here is the result from MySQL Client
MariaDB [test]> CREATE TABLE `connect_table` (
|
-> `id` int(10) NOT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer' `table_type`=odbc `block_size`=10 `tabname`='connect_table';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> show tables;
|
+----------------+
|
| Tables_in_test |
|
+----------------+
|
| connect_table |
|
+----------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select * from connect_table;
|
ERROR 1296 (HY000): Got error 174 '[unixODBC][Driver Manager]Can't open lib '/opt/lib/libmsodbcsql-11.0.so.2270.0' : file not found' from CONNECT
|