Updated on July 2nd, 2008.

As you may know, I develop a lot of new web applications using Ruby on Rails, and to completely buy in to the whole “cult that is Rails”, I purchased a Macbook Pro for development. I am really happy with it, but one of the problems I ran across is the fact that there’s no built-in or prepackaged way to connect to Microsoft SQL Server which I use at work for a lot of my projects.

This guide is written from my notes and should give you the tools you need to get up and running. Some of this information comes from the Ruby on Rails wiki, but some things there are a bit misleading.

This now works on Leopard too!

Getting everything ready

We’ll build everything we need from source. Open up your favorite terminal and create the following folders:

 
mkdir src
mkdir /Library/ODBC

We’ll download all of the source files into the src/ folder so we don’t litter up the hard drive with miscellaneous files.

Next, install Xcode and MacPorts, and be sure to update your path per the Macports instructions.

Install wget.

sudo port install wget

Install and configure FreeTDS

  wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
  tar zvxf freetds-stable.tgz
  cd freetds-0.82
  ./configure

Attention Leopard Users!
You need to do an additional step here:

cp /usr/bin/glibtool libtool

Then you can continue on…

  make
  sudo make install
  cd ..
  rm -rf freetds-0.82/

To test the configuration, run tsql -C. You’ll see the below output or something similar.


  Compile-time settings (established with the "configure" script):
                             Version: freetds v0.64
      MS db-lib source compatibility: no
         Sybase binary compatibility: unknown
                       Thread safety: yes
                       iconv library: yes
                         TDS version: 5.0
                               iODBC: yes
                            unixodbc: no


Next, configure FreeTDS by editing /usr/local/etc/freetds.conf. Add the following code to the bottom of this file:

  
  [MY_SERVER_NAME]
    host = db.mydomain.com
    port = 1433
    tds version = 8.0
  
  

The MY_SERVER_NAME placeholder is an identifier for your server. Examples might be PRODUCTION_RAILS_SERVER or something t hat helps you identify it in other configuration files.

Configuring iODBC to play nice

If you’ve ever configured the ODBCUnix packages for Linux then you might be familiar with this section. I tried to use a configuration that I’ve used on Ubuntu before but with no luck. It turns out that there are a few extra steps when it comes to OS X and ODBC with FreeTDS.

iODBC is going to look for the configuration files in /Library/ODBC which was not created on my system. Create that folder if it’s not there.

mkdir /Library/ODBC

Edit the file /Library/ODBC/odbcinst.ini and place the following contents in that file:

    [ODBC Drivers]
    TDS = Installed
 
    [TDS]
    Description = FreeTDS
    Driver = /usr/local/lib/libtdsodbc.so
    Setup = /usr/local/lib/libtdsodbc.so
    
  

This file basically binds FreeTDS to iODBC and is really, really important to getting this whole thing working. Make sure that you have both the Driver and Setup paths correct (they do in fact point to the same file.)

Finally, tie it all togoether by editing the file /Library/ODBC/odbc.ini and using this code:

  [ODBC Data Sources]
  MY_SERVER_NAME = TDS
 
  [MY_DSN]
  Driver = /usr/local/lib/libtdsodbc.so
  Description = Description of this database connection
  Trace = yes
  TraceFile = /tmp/odbc.log
  Servername = MY_SERVER_NAME
  Database = MY_DATABSE_NAME

Test the connection.

 iodbctest "dsn=MY_DSN;uid=username;pwd=secret"

You should get some sort of message saying it connected successfully.

. If you got a message saying that the data source name could not be found then you need to check your configuration. If your computer just seems to hang for ever without responding, check your Internet connection and check to see if the DBA has firewalled your machine off.

Getting Rails to work

Assuming that Rails is already installed, you need to install two additional libraries for Ruby.

  cd ~/src
 
  wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9995.tar.gz
  tar -zxvf ruby-odbc-0.9995.tar.gz
  cd ruby-odbc-0.9995
  ruby extconf.rb
  make
  sudo make install
  cd ..
  rm -rf ruby-odbc-0.9995/
 
  wget http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz
  tar -zxvf ruby-dbi-all-0.0.23.tar.gz
  cd ruby-dbi-all
  ruby setup.rb config --with=dbi,dbd_odbc
  ruby setup.rb setup
  sudo ruby setup.rb install
  cd ..
  rm -rf ruby-dbi-all/
 
  wget http://rubyforge.org/frs/download.php/33959/dbi-0.2.0.tar.gz
  tar -zxvf dbi-0.2.0.tar.gz
  cd dbi-0.2.0
  ruby setup.rb config --with=dbi,dbd_odbc
  ruby setup.rb setup
  sudo ruby setup.rb install
  cd ..
  rm -rf dbi-0.2.0
 
  

That’s it. That should get everything working so you can connect via Ruby.

. To add additionlal database servers, add them to the freetds.conf file. To add additional DSNs that point to databases on one of the existing servers in freetds.conf, change odbc.ini in the /Library/ODBC folder.

Database configuration in Rails

Here’s the database.yml configuration file for a typicall Rails application using a DSN.

  production:
    database: database_name
    adapter: sqlserver
    mode: odbc
    dsn: MY_DSN
    username: foo
    password: password