CODEWITHSUNDEEP
sql-serverosx-mountain-lionhomebrewfreetdsunixodbc
Mark Richman
Mark Richman

Reputation: 29710

Can't connect to SQL Server using isql on Mac OS X

I have installed freetds using Homebrew. My configuration is as follows:

odbcinst -j:

unixODBC 2.3.1
DRIVERS............: /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/Cellar/unixodbc/2.3.1/etc/ODBCDataSources
USER DATA SOURCES..: /Users/mark.richman/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini:

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

/usr/local/Cellar/freetds/0.91/etc/freetds.conf:

[db.mydomain.local]
host = db.mydomain.local
port = 1433
tds version = 8.0
client charset = UTF-8

~/.odbc.ini:

[MomImporterDSN]
Description = Mom Importer
Driver = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
Servername = db.mydomain.local
Database = MomImporter
UserName = mark.richman
Password = mypassword

When I try isql -v MomImporterDSN I get the following error:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

My /tmp/odbc.log doesn't seem particularly useful:

    [ODBC][14514][1373290079.248688][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x7fcbe1004c00
[ODBC][14514][1373290079.249314][SQLAllocHandle.c][375]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fcbe1004c00
[ODBC][14514][1373290079.249470][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7fcbe1024800
[ODBC][14514][1373290079.249712][SQLConnect.c][3700]
        Entry:
            Connection = 0x7fcbe1024800
            Server Name = [MOMImporterDSN][length = 14 (SQL_NTS)]
            User Name = [NULL]
            Authentication = [NULL]
        UNICODE Using encoding ASCII 'char' and UNICODE 'UCS-2-INTERNAL'

        DIAG [01000] [FreeTDS][SQL Server]Adaptive Server connection failed

        DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source

[ODBC][14514][1373290079.703504][SQLConnect.c][4067]
        Exit:[SQL_ERROR]
[ODBC][14514][1373290079.704257][SQLError.c][434]
        Entry:
            Connection = 0x7fcbe1024800
            SQLState = 0x7fff5a823526
            Native = 0x7fff5a823520
            Message Text = 0x7fff5a823530
            Buffer Length = 500
            Text Len Ptr = 0x7fff5a82351e
[ODBC][14514][1373290079.704559][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = S1000
            Native = 0x7fff5a823520 -> 0
            Message Text = [[unixODBC][FreeTDS][SQL Server]Unable to connect to data source]
[ODBC][14514][1373290079.704777][SQLError.c][434]
        Entry:
            Connection = 0x7fcbe1024800
            SQLState = 0x7fff5a823526
            Native = 0x7fff5a823520
            Message Text = 0x7fff5a823530
            Buffer Length = 500
            Text Len Ptr = 0x7fff5a82351e
[ODBC][14514][1373290079.704996][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = 01000
            Native = 0x7fff5a823520 -> 20002
            Message Text = [[unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed]
[ODBC][14514][1373290079.705172][SQLError.c][434]
        Entry:
            Connection = 0x7fcbe1024800
            SQLState = 0x7fff5a823526
            Native = 0x7fff5a823520
            Message Text = 0x7fff5a823530
            Buffer Length = 500
            Text Len Ptr = 0x7fff5a82351e
[ODBC][14514][1373290079.705357][SQLError.c][471]
        Exit:[SQL_NO_DATA]
[ODBC][14514][1373290079.705583][SQLError.c][514]
        Entry:
            Environment = 0x7fcbe1004c00
            SQLState = 0x7fff5a823526
            Native = 0x7fff5a823520
            Message Text = 0x7fff5a823530
            Buffer Length = 500
            Text Len Ptr = 0x7fff5a82351e
[ODBC][14514][1373290079.705763][SQLError.c][551]
        Exit:[SQL_NO_DATA]
[ODBC][14514][1373290079.706061][SQLFreeHandle.c][284]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fcbe1024800
[ODBC][14514][1373290079.706195][SQLFreeHandle.c][333]
        Exit:[SQL_SUCCESS]
[ODBC][14514][1373290079.706415][SQLFreeHandle.c][219]
        Entry:
            Handle Type = 1
            Input Handle = 0x7fcbe1004c00

I'm using Mountain Lion 10.8.4, unixodbc 2.3.1, freetds 0.91, and SQL Server 2008.

Thanks!

Upvotes: 0

Views: 2905

Answers (2)

Kaustav Sen
Kaustav Sen

Reputation: 21

Follow all instructions given in https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX

However, there are few additional steps and hacks if isql/osql doesn't work(assuming tsql is working properly). They may sound strange, but solved the issue (isql/osql not connecting although tsql works fine) for me.

Each of the steps listed below is an outcome of debugging the osql error message.

At one or more steps, you may require additional root privileges. Grant them as necessary and revoke once the pyodbc connectivity is achieved.

  1. The files odbc.ini and odbcinst.ini should also be present in /Users/<username>/etc, apart from the official doc specified location.

  2. There should be a copy of odbcinst.ini in /(root directory) as well.

  3. The file freetds.conf should be present in /usr/local/etc directory.

  4. If you are using SQL server 2014 and above, tds version should be set to 7.4. Do not set tds version to 8 since it is just an alias for 7.1.

  5. Check whether the file /usr/local/lib/libtdsodbc.so has execution privileges. If not, grant 766/744/777(in worst case) privileges via chmod. This step is crucial for running osql/isql and in turn pyodbc connection from python.

After these steps, re-check once the contents of odbc.ini, odbcinst.ini and freetds.conf and confirm they are as per the documentation (https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX)

Run isql/osql.

If everything works fine now, you are all set up to connect to SQLServer from python. The following snippet shows the basic connection process.

import pyodbc

connection = pyodbc.connect('DSN=MYMSSQL;UID=<user_id for db_login>;PWD=<password_for db_login>)
cursor = connection.cursor()

result = cursor.execute("select @@VERSION").fetchall()
print(result)

cursor.close()
connection.close()

Upvotes: 2

meda
meda

Reputation: 45490

You can fix this error by specifying the TDS version in ODBC setting

[MomImporterDSN]
Description = Mom Importer
Driver = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
TDS_Version = 8.0
Servername = db.mydomain.local
Database = MomImporter
UserName = mark.richman
Password = mypassword

Upvotes: 0

Related Questions