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.
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.
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.
Here’s where we tell unixODBC which ODBC drivers the system supports. We’ll add the following text:
1 2 3 4 5 6 7
Save that file. Next up we should configure FreeTDS to define our data sources.
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:
1 2 3 4 5
One last piece of configuration is needed. This is the general system datasources configuration.
The following should be fairly obvious configuration:
1 2 3 4
Step 4: Try it out
Using the unixODBC tool, test out your connection:
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.
With that installed, let’s test it out!
1 2 3 4
If you got a date back, then your connection is working properly. Huzzah!