Every once in a while I need to access a Microsoft SQL Server database from my Mac, usually to create a data migration script. In the past I used a commercial ODBC driver. But ODBC on OS X was deprecated ages ago.
In this post, I’ll talk about my current stack for accessing SQL Server databases via Ruby and Python and how to set it up. Note that I do not use homebrew, so these are all native installs.
You will need Xcode and the command line tools installed, which of course you already have.
FreeTDS is a set of libraries that one can use from UNIX based systems to natively talk to SQL Server. Download it from their stable release link to your
Downloads folder (current version 0.91 as of writing).
Then, to build and install it:
1 2 3 4 5
To have FreeTDS default to the newer protocols for newer SQL servers, edit
/usr/local/etc/freetds.conf and set:
1 2 3
This works with SQL Server 2000, 2005 and 2008.
You need the tiny_tds Ruby gem to access SQL Server via FreeTDS from Ruby. I had a lot of trouble getting this gem to build on OS X 10.8.4 with Ruby 1.9.3 under RVM until I figured out the parameters needed to make it work with my FreeTDS and Apple’s
iconv library. Use the following rather long command:
This compiles the gem with Apple’s
iconv lib and the FreeTDS we just installed.
After that, accessing a SQL Server database is easy. Here’s a script to dump a table or a query to a CSV file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
For Python, I tried the pymssql language extension. Installing this was quite easy:
1 2 3
And in a simple script:
1 2 3 4 5 6 7 8
Note the use of Python raw strings for the host and password.
If you are old school like me and want access to SQL Server from the command line, you really need the old
isql command line. FreeTDS does have a
tsql command to test access from the command line, but it is not fully featured.
1 2 3
You also need to recompile FreeTDS with support for
unixODBC, so go back to your FreeTDS folder and:
1 2 3 4
Following the instructions at http://www.unixodbc.org/doc/FreeTDS.html, you need to register FreeTDS and create your first data source. My files look like this:
1 2 3 4
1 2 3 4 5 6 7 8
To see if this is working, use the
You can then access this database using good old
Note that you may need to put
" characters around the password if there are special characters in it.
It’s quite sad that the popular Open Source databases like MySQL, PostgreSQL and MongoDB all have wonderful, fast, easy to install, native libraries for OS X and Linux, but the big commercial databases like SQL Server and Oracle still require these unpleasant hacks, ancient deprecated libraries or worse, Java!
A lot of companies are moving their servers off Microsoft Windows and on to Linux, but they still need access to legacy data, and these hacks are just not that elegant or robust enough for true production use. Until these companies can migrate their databases off Microsoft and Oracle, they are going to have to live in transition and hope these hacks hold.
I think it’s in Microsoft and Oracle’s interest to release public libraries to access their databases from anywhere, including other platforms, in order to stay relevant and in the game.