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](http://stackoverflow.com/questions/16925825/having-troubles-with-unixodbc-freetds-and-pyodbc/17113723#17113723) on another question helps - and try TDS version = 8.0... — Benny Hill, Jun 21 '16 at 03:13
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, Jun 21 '16 at 12:32
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: http://www.freetds.org/userguide/choosingtdsprotocol.htm — FlipperPA, Jun 22 '16 at 20:40
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, Jun 23 '16 at 03:31

score 1 · Answer 1 · answered Jun 21 '16 at 02:10

1

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.

answered Jun 21 '16 at 02:10

James K. Lowden

7,574
1
16
31

Thanks James, very helpful and I will follow your guidance. One caveat: I don't WANT to use iODBC. I am trying to use unixODBC because, as you noted, it seems more popular/better supported. However, based on the second error I noted above, pyODBC (v 3.0.10) seems to be using iODBC. And I don't know why. And it makes me sad. – RJH2 Jun 21 '16 at 12:42
And yes, many moving parts. But I don't think my set-up is that weird, is it? I'm just a guy, with a mac, trying to read a sql table into a pandas dataframe, using jupyter notebook. If there is an easier way, please, please, please tell me. – RJH2 Jun 21 '16 at 12:47
I've narrowed my question to what I think is the issue, here: http://stackoverflow.com/questions/37947482/pyodbc-refuses-to-look-for-unixodbc-instead-looks-for-iodbc – RJH2 Jun 21 '16 at 14:42

score 0 · Answer 2 · answered Jun 22 '16 at 20:43

0

Here's an example I think would work for you. If you're using FreeTDS 0.95, you can use TDS Version 7.3, if you're using 0.82 or less, use 7.1. I've never bothered with testing osql with this stack, if tsql and isql work, you should be able to get the rest working, but the nuances of the configuration and connection are tricky:

freetds.conf:

[MYSERVERNAME]
    host = MYSERVERNAME.host.com
    port = 1433
    tds version = 7.2

odbc.ini:

[MYSERVERNAME]
    Driver = FreeTDS
    Server = MYSERVERNAME.host.com
    Port = 1433
    TDS_Version = 7.2

odbcinst.ini:

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

In Python:

connection = pyodbc.connect(r'DRIVER={FreeTDS};SERVER=MYSERVERNAME.host.com;PORT=1433;DATABASE=Database name;UID=Database Username;PWD=DatabasePasswd;TDS_Version=7.2')

TDS Version 8.0 does not exist. 7.2 is the highest supported in FreeTDS 0.91. See here to explain the confusion: http://www.freetds.org/userguide/choosingtdsprotocol.htm

If you're still having issues, try testing with tsql and isql to test the FreeTDS and unixODBC layers of the connection stack respectively. Good luck!

answered Jun 22 '16 at 20:43

FlipperPA

13,607
4
39
71

Thanks FlipperPA, but no joy. I've tried everything the full range of FreeTDS versions (7.0, 7.2, 7.3 and 8.0, just in case) and none worked. Used your syntax for the 3 files and the python code (twice), and I get the same error that seems to reference the iODBC error. Also tested isql and sql and able to connect with both. This seems to be a pyodbc issue. – RJH2 Jun 23 '16 at 03:28
When you do a `tsql -C`, does it say "yes" for both iODBC and unixodbc? To test iODBC connectivity, I believe there's an `odbctest` command you should use similar to `isql` in `iodbc/samples`. Is uninstalling iODBC and option if it is there? – FlipperPA Jun 23 '16 at 10:42
Yes to unixodbc, no to iODBC. Which is good, right? `Version: freetds v1.00 freetds.conf directory: /usr/local/Cellar/freetds/1.00/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 7.3 iODBC: no unixodbc: yes ...` – RJH2 Jun 23 '16 at 11:48
I thought about uninstalling iODBC. But I understand that could break a lot of other stuff, as a lot of commercial software uses it. Plus, it seems clear that pyodbc is looking for iODBC, and I'm not sure getting rid of it would cause it to then default to unixODBC. I'm guessing it would just say iODBC is missing. Is there something else in the system that overrides what or where pyodbc looks for its drivers? – RJH2 Jun 23 '16 at 12:12
I've seen a lot with FreeTDS, but this is a new one. I haven't touched FreeTDS 1.0 either. Any chance you could try it with version 0.95? I'm grasping at straws here, to be honest. – FlipperPA Jun 23 '16 at 17:59
Man, I'll give anything a try at this point. Can you specify a version through brew install? If not, I guess I need build it from an archived version? – RJH2 Jun 23 '16 at 23:08
I don't have my Mac handy, but 'brew search freetds' and then look for a package name like 'freetds-0.95' IIRC. – FlipperPA Jun 24 '16 at 11:19

score 0 · Accepted Answer · edited May 23 '17 at 11:52

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.

score 0 · Answer 4 · answered Mar 14 '19 at 18:21

0

Just uninstalling pyodbc and reinstalling it back solved the problem for me.

answered Mar 14 '19 at 18:21

S_Dhungel

73
5

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

4 Answers4

Linked