Connecting to SQL Server is one task I have to do regularly when I work on Rails applications for clients that use Microsoft technologies. I usually set my machine up and don’t really worry too much about how it all comes together.

Recently I had to do the installation completely from scratch. I followed Ken Collins’ comprehensive walkthrough, but I ran into a problem – I wanted to use RVM. Ken’s tutorial uses MacPorts to install the Ruby ODBC bindings, and I wasn’t using Ruby via MacPorts.

Everything out there wasn’t working for me even when I followed Ken’s article on RVM.

Here’s what ended up working for me.

First, I used MacPorts. Then I installed the ports I needed:

sudo port install unixodbc
sudo port install freetds +odbc

I modified /opt/local/etc/freetds/freetds.conf to look like this:

[my_dev_server]
  host = 192.168.1.58
  port = 1433
  tds version = 8.0

Then I modified /opt/local/odbcinst.ini to point to my FreeTDS configuration:

[FreeTDS]
Decscription = FreeTDS driver for SQLServer
Driver = /opt/local/lib/libtdsodbc.so
Setup = /opt/local/lib/libtdsodbc.so
FileUsage = 1

Finally I modified /opt/local/odbc.ini and created my ODBC DSN.

[my_dev_server_dsn]
Driver=FreeTDS
Description=My Server Connection
Servername=my_dev_server
Server=my_dev_server
Port=1433
Database=killer_app

Note that the servername matches the servername defined in the FreeTDS configuration file.

Then I went and installed the gems I needed for my project

gem install ruby-odbc
gem install dbi  -v=0.4.1
gem install dbd-odbc -v=0.2.4
gem install activerecord-sqlserver-adapter -v=2.3.9

But when I ran my Rails application, attempts to connect to models failed.

ODBC::Error: IM002 (0) [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

It’s using iODBC which comes with OSX. It wasn’t using UnixODBC at all!

To fix that, I had to reinstall the ruby-odbc gem and instruct it to use the unixodbc path. This is what ultimately fixed things for me:

gem install ruby-odbc -- --with-odbc-dir=/opt/local

After that, everything worked again!