Error connecting to MS SQL Server using pyODBC, unixODBC and FreeTDS (on a Mac)

Question

I am getting an error when trying to connect to a MS SQL server in python, using the pyODBC --> unixODBC --> FreeTDS --> MS SQL stack. I’ve spent a lot of time on this, and there are some great resources if you’ve reached this question with more fundamental problems getting a connection to work here and here.

However, my question is regarding an error that (I think) is very close to the finish line of this very frustrating experience. Specifically, this code in jupyter notebook:

pyodbc.connect(
    'DRIVER=/usr/local/lib/libtdsodbc.so;'
    'SERVER=MyServerIP;'
    'PORT=1433;'
    'DATABASE= DatabaseName;'
    'UID=MyUsername;'
    'PWD=MyPassword')

Is giving me this error:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-7-d6b29b647116> in <module>()
      1 pyodbc.connect(
----> 2     'DRIVER = /usr/local/lib/libtdsodbc.so;'
      3     'SERVER = MyServerIP;'
      4     'PORT = 1433;'
      5     'DATABASE = DatabaseName'

Error: ('HY000', '[]  (20013) (SQLDriverConnect)’)

And if I replace 'DRIVER=/usr/local/lib/libtdsodbc.so;' with 'DRIVER=FreeTDS;' I get:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-12-607f0d66e615> in <module>()
      1 pyodbc.connect(
----> 2     'DRIVER=FreeTDS;'
      3     'SERVER= MyServerIP;'
      4     'PORT=1433;'
      5     'DATABASE= DatabaseName;'

Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')

Which leads me to believe there is something wrong with the unixODBC --> FreeTDS connection because of the reference to iODBC. Said differently, unless I specifically provide a path to the FreeTDS driver, it seems to ignore my odbcinst.ini and odbc.ini files, which reference FreeTDS and its location as my driver (see below)

When running tsql and isql from the terminal, both yield good connections with the server.

However, when I run the osql, I get the following error:

$ osql -S MyServerIP -U MyUsername -P MyPassword
checking shared odbc libraries linked to isql for default directories...
/usr/local/bin/osql: line 53: ldd: command not found
error: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/strings: can't open file:  (No such file or directory)
osql: problem: no potential directory strings in "/usr/local/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
    reading /Users/myname/.odbc.ini
[MyServerIP] not found in /Users/myname/.odbc.ini
    cannot read "/odbc.ini"
osql: error: unable to locate MyServerIP in any odbc.ini

Background on my setup

My connection was built (and rebuilt and rebuilt) using the two resources linked in the first paragraph and my complete set up looks like this:

Environment

Mac OSX 10.11.5

Microsoft SQL Server 2012 – AWS EC2 instance (cloud)

Anaconda 4.0

Python 3.5.1

Jupyter Notebook 4.1.0

Connection Stack

unixODBC – installed using homebrew

FreeTDS – installed using homebrew with the command: `$ brew install

freetds --with-unixodbc`

pyODBC 3.0.10 – installed using conda install

MS SQL – AWS EC2 instance (cloud)

Reference Files

My freetds.conf file reads like this:

[MYSERVERNAME]
    host = MyServerIP
    port = 1433
    tds version = 7.3
    client charset = UTF-8

My odbcinst.ini file reads like this:

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1

My odbc.ini file reads like this:

[MYSERVERNAME]
Driver = FreeTDS
Server = MyServerIP
Port = 1433

I am at a complete loss, having spent far more hours than I should have on this. If anyone has any suggestions, I’d be forever grateful.

Thanks.

See if my answer on another question helps - and try TDS version = 8.0... — Benny Hill
Thanks @BennyHill, strictly following the instructions in your link caused me to fail making a connection through the isql command. As is above, both my tsql and isql tests create connections. I'm more and more convinced it has something to do with a failure in the pyODBC --> unixODBC connection. But can't figure out what. — RJH2
Two things, (1) please use DRIVER={FreeTDS} in your pyodbc.connect() and (2) do NOT use TDS version 8.0, it is not a real TDS version. See: freetds.org/userguide/choosingtdsprotocol.htm — FlipperPA
Made the changes you suggested, but no joy. As in my comment below, but also added the braces around FreeTDS. Same error: Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen({FreeTDS}, 6): image not found (0) (SQLDriverConnect)') — RJH2

James K. Lowden James K. Lowden · Accepted Answer · 2016-06-21T02:10:00

There are a lot of moving parts in your question. Not only do you have Notebook-on-Python-on-ODBC, but you have iODBC and OS X. Yikes!

The question boils down to this: Where does iODBC look for odbc.ini? I don't know of an ODBC function that reports that information.

Because it's so doggone complicated, I suggest using OS X dtruss(1). Capture the output to a file, and grep for odbc.ini and/or open commands. Once you know where it's looking, you can put your file there, and just follow directions. ;-)

The reason the osql script doesn't work on OS X is that no one ever cared to make it work, or ever complained about it on the FreeTDS mailing list. The first message is a doozy:

/usr/local/bin/osql: line 53: ldd: command not found

I work around that with

$ command -V ldd
ldd is aliased to `otool -L'

That might help. OTOH, the script was written with unixODBC in mind, because it's so much more popular.

Error connecting to MS SQL Server using pyODBC, unixODBC and FreeTDS (on a Mac)

4 Answers