RJH2 RJH2 - 1 year ago 580
Python Question

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

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:

'DATABASE= DatabaseName;'

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:


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:

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

My odbcinst.ini file reads like this:

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:

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.


Answer Source

Well, we solved it -- with the help of a lot of people on this page and over here, chasing down a lot of blind alleys.

As (eventually) suspected, it was the pyodbc link in the connection. I was using pyodbc v3.0.10, by downloading from the Anaconda package repository. The solution was v.3.0.9. Once I uninstalled v3.0.10, downloaded v3.0.9 from the pypi repository and then built and installed my own conda package... it worked.

The steps I took were as follows (note these were specific to an anaconda environment):

conda uninstall pyodbc

conda skeleton pypi pyodbc --version 3.0.9

conda build pyodbc

conda install pyodbc=3.0.9 --use-local

Once I went back to my Jupyter notebook and ran the same code above, it created a good connection.

I do not know what is wrong with v.3.0.10, or if it's just the files that anaconda.org has on their repository. I've posted something on the pyodbc github page also, but it doesn't look that active.

Anyway, thank you for everyone's help. I hope this saves someone some time.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download