SJKingston.com

I have recently been trying to get ODBC working nicely (ha) on Linux so I can talk to a Microsoft SQL Server (MSSQL) that is not under my direct control (otherwise I would just rip it out and replace it with a PostgreSQL one instead). Unfortunately, it seems most of the documentation for the various free ODBC tools (unixODBC and FreeTDS) are out of date and in some cases, just plain incorrect (namely DSN-less configuration: don’t waste your time, it’s completely broken).

Although you need FreeTDS (exactly why I’m still not sure), all of the configuration is done in unixODBC (and indeed this is what any application will call to connect via ODBC). FreeTDS kinda just sits in the background being used as a driver for the other guy.

Here is how I got it working:

1. emerge -a freetds unixODBC (make sure you have both mssql and odbc USE flags set on the former).

2. rm -f /etc/unixODBC/odbc* (yes, remove them—we’ll recreate them in a sec).

3. Re-create /etc/unixODBC/odbc.ini with the following:

   [FreeTDS]
   Driver = /usr/lib/libtdsodbc.so
   [MSSQL]
   Driver = FreeTDS
   Address = 1.2.3.4
   Port = 1433
   TDS Version = 8.0
   Database = test
   

   This file creates the ODBC DSN structure you will use when connecting. In the above example, the DSN is called MSSQL. Replace it with another name if you would like.

   There are a few important things to note here. Take note of the Address attribute: this forces unixODBC to connect directly to the server, rather than through FreeTDS’s data sources (which are broken). So ignore any documentation that tells you to use Server or ServerName. 1433 is the default MSSQL port, so only change this line if your server is running on a different port.

4. Enter into the /etc/unixODBC directory and symlink the newly recreated odbc.ini to odbcinst.ini:

   # ln -s odbc.ini odbcinst.ini
   

   This is one of the annoying bits missing from any of the documentation, and I only found it by straceing the isql command. One or the other doesn’t work; you need both odbc.ini and odbcinst.ini.

5. No matter what the FreeTDS documentation tells you, ignore /etc/freetds.conf. Since we’re telling unixODBC the hostname to connect to, we don’t need any data sources. Forget the file exists.

6. Test connecting to the MSSQL server:

   $ isql -v -s MSSQL user pass
   

   You should get a help message printed and a SQL> prompt:

   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL>
   

   If not, the -v option should have given you a (somewhat useful) error.

7. You can now connect to your MSSQL server via ODBC in your application, using the DSN you specified above. If you are using pyodbc (the recommended driver for SQLalchemy), you will want a connection string similar to:

   >>> 'mssql:///?' + urllib.quote_plus('dsn=MSSQL;uid=user;pwd=pass', '=')
   'mssql:///?dsn=MSSQL%3Buid=user%3Bpwd=pass'
   

   This is contrary to what the SQLalchemy docs say, since they focus on DSN-less connection strings (which as I mentioned before are completely broken). Note you do not want to quote the =, so we give it as a safe option to urllib.quote_plus (that was a time-consuming mistake I made).

By the way, if you’re using Gentoo you can grab the pyodbc ebuild from SJKWI’s overlay, as it’s not in the Portage tree (yet?). Instructions for adding the overlay to layman are in the repo’s README.