This is way hard than it should be, but once you go "off the rails" not everything is candy & roses and chocolate covered bunnies. Of course, there is still a great community of talented folks producing stuff that makes all of this work.

The trick is to know what to look for. This post is for you, dear reader, to hopefully avoid all of the trial and error that I went through and hopefully get it to just work.

So, in my quest to connect a little ruby app on Linux to a SQL Server 2008 instance on Windows, I stumbled across the magical combination that allowed me to perform this task.

All of these commands were run on Ubuntu 10.4.

Step 1: Get unixODBC.

You'll be connecting via ODBC, so let's grab this one. There's also iODBC, but I didn't give that one a try.

sudo apt-get install unixodbc-dev

Step 2: Get FreeTDS

TDS stands for "Table Data Stream" and is the communication protocol that Sybase & SQL Server use. There are plenty of commercial drivers out there for just about every operating system, but one stood out to me... FreeTDS. I'll give you 3 guesses why I liked this one (but you'll only need 1).

We'll also need the ODBC connector for this.

sudo apt-get install freetds tdsodbc

Step 3: Configure-shit

There are 3 INI files that we need to modify. When I did this on my Mac, they were in /usr/local/etc, but on Ubuntu it was all in the /etc directory.

Confirm that the FreeDTS & TDSODBC drivers are in place by looking in `/usr/lib/odbc` for the files `libtdsodbc.so` and `libtdsS.so`. We'll be using those in the next step.

Using your favorite editor, open up `/etc/odbcinst.ini`. This file might not exist yet, that's okay.

sudo vim /etc/odbcinst.ini

Here's where we tell unixODBC which ODBC drivers the system supports. We'll add the following text:

[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5

Save that file. Next up we should configure FreeTDS to define our data sources.

sudo vim /etc/odbc/freetds/freetds.conf

There should be some example configurations in here. Also note that here is where you can un-comment a line to enable debug output if you're having trouble (I needed this). Here is my configuration:

#my sql server (SQL Server 2008)
[my_database]
host = 11.22.33.44 #no this isn't a real IP. you think I'm crazy?
port = 1433 #change this if you're running your db on a non-standard port
tds version = 8.0

One last piece of configuration is needed. This is the general system datasources configuration.

sudo vim /etc/odbc.ini

The following should be fairly obvious configuration:

[my_database] #note this doesn't have to match what's in freetds.conf
Servername = my_database #this matches what's in freetds.conf
Driver = FreeTDS #this matches the driver you configured in odbcinst.ini
Database = YOUR_DATABASE_HERE

Step 4: Try it out

Using the unixODBC tool, test out your connection:

isql my_database uid password

If you had any errors, you’ll get something like “Cannot SQLConnect”. If this happens, check your debug log (if you enabled it in freetds.conf). In my case I forgot to enable Mixed Mode authentication on the SQL Server side.

Step 5: Install the gem

We’ll be using a nice gem called tiny_tds.

gem install tiny_tds

With that installed, let’s test it out!
Launch irb:

require 'tiny_tds'
client = TinyTds::Client.new(:dataserver => 'my_database', :username=>'user', :password=>'password')
client.closed? # should be false, if it's true you probably had an error connecting
client.execute("SELECT getdate()").each {|row| puts row}

If you got a date back, then your connection is working properly. Huzzah!