Installing & Debugging ODBC on Mac OS X


I just spent nearly two full days in a bare knuckle brawl with my Macbook Pro trying to get it to talk to a corporate MS SQL Server. I had abandoned MSSQL more than a year ago in favor of PostgreSQL because of how much easier it is to work with PostgreSQL from a non-Microsoft stack. At that point I was R running on Linux and soon R running on OS X. As part of me changing roles at the company I work for, I’ve joined a team where everyone else uses Python. So I’m now trying to play nice with the Python guys. In addition to Python, I need to talk to corporate servers which happen to be Microsoft SQL Server.

So enough about why I was banging my head against the Mac ODBC wall. Here’s some things I didn’t understand until I fought with them for a day. Maybe I can save you some pain.

The diagram above shows the sort of stack I now have working. But when I started I didn’t even understand how the pieces fit together. Keep in mind that for better or worse, I’m using Macports and in some ways I really like letting Macports do the installation of things. Yes, I know Homebrew rocks and yada yada yada, but I’m not currently using Homebrew. I’m using Macports. So the things I describe below are highly biased to Macports, but much of it is highly applicable to any install method.

It’s pretty obvious that when debugging something not working we should move through each piece and make sure each little chunk works before going to the next piece. Of course in real life we just flail, randomly try things, and cuss a lot. Until we get all red in the face and take a step back. So let’s start at the beginning:

The installing process should look something like the following:

SQL Server: Make sure you can log into SQL Server from a windows box on the same network which you are ultimately trying to connect from. And make sure you log in using the same credentials which you want to use elsewhere.

FreeTDS: This is the driver which sits between the Mac ODBC layer and MS SQL Server. FreeTDS does the talking to MS SQL. So it’s really important, obviously. I ultimatly installed FreeTDS using the following Macports command:

sudo port install freetds +mssql +odbc +universal

the +odbc bit installs unixODBC and the +mssql and +universal bits are totally mysterious to me. The command line tool tsql comes with FreeTDS. In the debugging section I’ll comment more on using isql.

For what it’s worth, the current version as of this writing is freetds @0.92.405_0

UnixODBC: Comes along with FreeTDS. unixODBC includes the command line tool isql which is a lot like tsql. More on that below.

pyodbc: (Sept 2013 update: Macport of pyodbc is now named py27-pyodbc, the way God intended.) I didn’t think there was a Macport for pyodbc.  The naming pattern for python packages in Mac is pythonVersion-packagename. So, for example, Pandas is py27-pandas for the Python 2.7 version of Pandas. So I tried the following:

sudo port install py27-pyodbc

Which fails to find the package. So I pulled the tar ball and spend hours yesterday trying to get pyodbc to compile and work in Mac OS X including fighting with the setup.py and trying to learn about build directories and other black magic things about Python. Then I stumbled on a discussion of using the Macports to install pyodbc. It turns out that the Macport of pyodbc is called, confusingly, simply odbc. So the install command is this:

sudo port install py27-odbc

OK, so if I had simply installed pyodbc properly with Macports I probably could have saved myself 4 hours of pain. But I did learn some things along the way. Let me capture a few of those so hopefully future adventurers will be spared some pain.

Debugging:

Problem: At one point I kept getting the following type of error in Python:

pyodbc.Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen({SQL Server}, 6): image not found (0) (SQLDriverConnect)')

Solution: The clue here is the [iODBC] bit. iODBC is the default ODBC manager which now comes with Mac OS X. iODBC is a slightly less desirable ODBC manager than unixODBC. Despite elaborate comments found online where folks say, “iODBC works fine for me” most folks agree to use unixODBC and many have tried and failed to make iODBC work as expected. I spent a lot of time trying to figure out how to replace iODBC with unixODBC. It turns out the choice of whether to use iODBC or unixODBC happens when pyodbc is built and installed. Once I dropped back and installed freetds and pydobc as outlined above, I moved on to errors like the following:

ProgrammingError: ('42000', "[42000] [unixODBC][FreeTDS][SQL Server]Login failed for user 'sa'. (18456) (SQLDriverConnectW)")

You can see from that error message that I’m getting errors back through unixODBC and FreeTDS. Huge progress!

Problem: How can I tell if FreeTDS is properly installed?

Solution: Use the tsql command line program to connect to your DB. You’ll need to do something like the following:

tsql -S myserver -U username -P mypassword

The errors returned by tsql are fairly uninformative. You’ll likely see simply

There was a problem connecting to the server

or

Error 100 (severity 11):	unrecognized msgno
The good news is that FreeTDS only has one configuration file and only a couple of things are important in that file. The config file for FreeTDS (if installed by Macports) is /opt/local/etc/freetds/freetds.conf
The two things in freetds.conf which I found REALLY matter are port number and tds version. For my SQL Server 2008 I needed to set tds version to 7.2 by editing the [global] section of freetds.conf to look like:
[global]
     # TDS protocol version
     tds version = 7.2
Then I found that I also had to include port number for every server I wanted to connect to. This should not be needed because tsql allows passing port number using the -p switch. In my experience the -p switch resulted in a failure to connect, but putting the port number for the server in the freetds.conf file works. Note that my SQL Server instance uses the default port number so I didn’t think I would need this at all. But I do. My entry looks like this:
[MYSERVER]
   host = MYSERVER
   port = 1433

If you are connecting to all different vintages of SQL Server you may need to override the global tds version by using a different version in the server specific section. If you want some clues as to which TDS version you should be using, start with this chart from FreeTDS. If you get the port settings right and the names right, you’ll likely get a > prompt after you use tsql to connect to the DB. If you get a > prompt, you’re in pretty good shape!

Problem: How can I tell if unixODBC is properly installed?

Solution: First, make sure FreeTDS is installed properly. Seriously. Don’t skip that. After you’re able to connect using tsql, we need to configure some text files in order to move forward. unixODBC has three types of files to edit (paths assume installation using Macports):

Global driver configuration:

/opt/local/etc/odbcinst.ini

Global DSN configuration file:

/opt/local/etc/odbc.ini

Local DSN configuration file:

~/.odbc.ini

I elected to not even bother to create the Local DSN file and do everything from the two global files. What can I say? I’m global, that’s just how I roll.

The global driver config file (/opt/local/etc/odbcinst.ini) needs to contain a link to the FreeTDS driver. Mine looks like this:

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on Win32
Driver=/opt/local/lib/libtdsodbc.so
Setup=/opt/local/lib/libtdsodbc.so
UsageCount=1

From what I can tell, Macports does NOT create this file and it has to be manually created.

The DSN configuration file does not strictly have to be created. It’s possible to connect without DSN entries. But for the sake of testing, I highly recommend setting up at least one server DSN. You can find the DSN format in a lot of places online. Mine looks like this:

[MYSERVER]
Description         = Test to SQLServer
Driver              = FreeTDS
Trace               = Yes
TraceFile           = /tmp/sql.log
Database            = TechnicalProvisions
Servername          = MYSERVER
UserName            = myusername
Password            = mypassword
Port                = 1433
Protocol            = 7.2
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No

After you set up the odbcinst.ini and the odbc.ini you are ready to test unixODBC using isql. To connect do your version of the following at the commend prompt:

isql MYSERVER myusername mypassword

YOU MUST INCLUDE YOUR USERNAME AND PASSWORD in the command line. Yes, even though the username and password are in the DSN entry, they must be included in the command line. I lost the better part of an hour trying to change fix a broken connection that wasn’t broken, only because I didn’t include my username and password. The only error isql returns when username and password are missing is:

[ISQL]ERROR: Could not SQLConnect

Yeah, that’s part of why this crap is hard.

Now if you’ve got isql connecting, you’re ready to move on to testing pyodbc in Python. If you’ve installed pyodbc as outlined above and then set up your ODBC files and FreeTDS and tested each one, this should be a snap! I use Pandas for its DataFrame structure. And after getting each of the previous bits working, I was able to do this:

import pyodbc
import pandas
import pandas.io.sql as psql
cnxn = pyodbc.connect('DSN=MYSERVER;UID=myusername;PWD=mypassword' )
cursor = cnxn.cursor()
sql = ("SELECT * FROM dbo.pandasTest")
df = psql.frame_query(sql, cnxn)

And it worked! But worth noting, just like on the command line, if I failed to include my username and password, I would get a failed connection. In my case it was this:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
in ()
3 import pandas.io.sql as psql
4
----> 5 cnxn = pyodbc.connect('DSN=MYSERVER' )
6 cursor = cnxn.cursor()
7 sql = ("SELECT * FROM dbo.pandasTest")
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)')

Which, not unlike the isql error, is not particularly helpful.

Good luck. And may the ODBC be with you!

20 Comments

  1. Sergey says:

    Ten (!) years ago I tried to connect to MS SQL from Linux server exactly the same way. It was terrible!
    Don’t believe that everything is still the same.

  2. Gary Mawdsley says:

    A very helpful piece. The bit i remain struggling with is how the python library pyodbc gets installed.

    I’ve run the macport pyodbc install (where the naming issue above seems to have been sorted) and that seems to install ok, i.e. compiles up the binary – does it also install the python lib that presumably uses the binary – I’m guessing so but i cannot “import pyodbc” – i get a load error.

  3. Gary Mawdsley says:

    please don’t include my last comment – i figured it out now – simply macports failed to install pyodbc into my python virtualenv – had to manually copy the package into my site_packages folder under the python virtualenv – must be a macports bug i guess

  4. shahid says:

    This is the same ting happening to me in Perl, I dont know wht to do. Lets see if this article fix my problem.

    Thanks

  5. Tom says:

    This was incredibly helpful. I was eventually able to get a simple install via Homebrew (https://gist.github.com/tommct/5749453), but your post helped me understand a lot.

  6. Derek says:

    Thanks so much. Debugging iODBC was a total nightmare, but this post saved me!

  7. Justin says:

    Thanks JD, I’ve had to do this numerous times over the years and there’s always enough time in between that I just forget all the nuances of this. On OS X this has always been a pain point as well since iODBC is already installed by default (ugghh).

    You just saved me a bunch of “relearning just to forget again” time.
    Thanks =)

  8. Bryan says:

    Thank you so much. For some reason I could not get python to see the MacPorts version of FreeTDS when following your instructions. I added the Homebrew version (brew install freetds) and it is working.

  9. Tariq says:

    Thank you for posting this! Helped out a ton.
    I just want to point out that they realised that name for the pyodbc port “” py27-odbc ” naming is counterintuitive and have changed it to py27-pyodbc.

    So now this command should be used:
    sudo port install py27-pyodbc

  10. JD Long says:

    Thanks. I’ve updated the text to note that Macports has changed the name!

  11. Raj says:

    I have followed your step by step debugging procedure as mentioned in the post.My tsql works fine .But I keep getting stuck at the unixODBC part -

    [ISQL]ERROR: Could not SQLConnect

    Running it with verbose option shows –

    [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
    [01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
    [ISQL]ERROR: Could not SQLConnect

    I did put in my username/password.Any chance you encoutered this? Or have a possible clue how to go about it .

    Thanks!

  12. JD Long says:

    Sorry Raj. It’s all I can do to debug these problems when I have a full working environment in front of me. You might try using machine IP address instead of name, just to make sure it’s resolving properly.

    Good luck!

  13. Raj says:

    Thanks for the quick response JD! Direct IP doesnt resolve the issue.Did you need to set any variables to point to the odbc.ini and odbcinst.ini files?

  14. Raj says:

    I think I have the unixODBC part sorted out .Turns out the configuration name provided under freeTDS.conf had to precisely match the one in odbc.ini.

  15. jukart says:

    Never thougt I would be able to connect from my MAC to mssql until I found this description.

    You saved my life, thanks a lot.

  16. Thanks so much for this walk through. I followed your instructions and they worked perfectly. Well done. The only point that could be clearer are the relationship between the freeTDS and the unixodbs config file names eg: [MYSERVER]

    Thank you thank you!

  17. Tony says:

    Huge help here. Thanks so much.

    For a long time I battled tsql success, while isql connection failed with the following error(s):

    >isql DATASOURCE userid password -v

    [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
    [37000][unixODBC][FreeTDS][SQL Server]Login failed for user ‘username’.
    [37000][unixODBC][FreeTDS][SQL Server]Cannot open database “MYDB” requested by the login. The login failed.

    Setting the Database to tempdb in odbc.ini exposed the problem: access to “MYDB”.

  18. Calvin Giles says:

    Thanks for the excellent post.

    One issue I ran into for some time was due to having a unicode character in the database password. It results in Error 20017 (EOF). The solution was to change the password to one that can be encoded in ascii!! After that everything worked a treat.

    Not sure if there is a better way.

Leave a Reply