Getting your Mac to talk to SQLServer via ActiveRecord is akin to watching Gretel give commentary on who’s-picking-who’s-nose on the latest episode of Big Brother.

Though there’s a few nuggets of information floating around on the web—New Auburns’s article, the Rails wiki, and iamrichardson.com’s 2 part series—I wanted something that integrated with my preferred macports setup and didn’t involve recompiling ruby-dbi.

Update: Thanks to Maun Suang Boey from macports we’ve been able to update the rb-dbi port to include support for odbc, making this process a whole lot simpler.

1. Install Ruby-DBI with ODBC
2. Install FreeTDS
3. Test FreeTDS
4. Configure FreeTDS
5. Add the FreeTDS ODBC driver
6. Create an ODBC datasource
7. Test the ODBC connection
8. Configure Rails

Notice no need for unix-odbc? OSX ships with ODBC and an ODBC admin tool to make our lives just that little bit easier.

Install Ruby-DBI with FreeTDS

Make sure you’ve synced with the latest macports tree:

sudo port sync

and install away:

sudo port install rb-dbi +dbd_odbc

If you you’ve already got the rb-dbi or the old rb-dbd-mysql and rb-dbd-postgresql ports installed you’ll probably need to remove them first.

You could also add +dbd_mysql and +dbd_pg if you like.

Install FreeTDS

$ sudo port install freetds

easy peasy.

Test FreeTDS

We need to test that we can connect to the SQL server using FreeTDS’s diagnostic tool, tsql tool. We must make sure this works before proceeding with modifying configs and setting up ODBC.

$ man tsql
NAME
       tsql - utility to test FreeTDS connections and queries

SYNOPSIS
       tsql   {-S servername [-I inside] | -H hostname -p port}
              -U username [-P password]
...

Plug in the values to your SQL server as below, specifying your own values for SQL_SERVER_ADDR, USER and PASSWORD:

$ tsql -H SQL_SERVER_ADDR -p 1433 -U USER -P PASSWORD
locale is "C/en_US.UTF-8/C/C/C/C" 
locale charset is "UTF-8" 
1>

We’ve got the 1> prompt, which means we’re succesfully connected. Let’s run a DB query just to see things are working, not forgetting the all-important ‘go’:

1> use jobfutures;
2> select top 1 memberId from tblMembers;
3> go
memberId
263

Woohoo! Now we’re talkin.

Configure FreeTDS

Now we’ve connected to SQL server using tsql we add a server entry to the FreeTDS config file, which can be found at /opt/local/etc/freetds/freetds.conf

[somesqlserver]
  host = HOST_ADDRESS
  port = 1433
  tds version = 8.0 # for SQL2000

Throw out the host and password parameters we needed before, we can now just refer to it using the server name somesqlserver:

$ tsql -S somesqlserver -U sa -P SA_USER_PASSWORD
locale is "C/en_US.UTF-8/C/C/C/C" 
locale charset is "UTF-8" 
1> 

Add the FreeTDS ODBC driver

OS X ships with a small but handy tool ODBC Administrator, found in /Applications/Utilities, which you can use to manage your ODBC settings. Fire it up and go to the Drivers tab. Add the TDS driver with the following settings:

Description: TDS
Driver file: /opt/local/lib/libtdsodbc.so
Setup file:  /opt/local/lib/libtdsodbc.so
Define as:   System

Create an ODBC datasource

Add a new User DSN In ODBC Administrator with a name of somesqlserver (our Rails config will refer to this DSN) and the following Key/Values:

ServerName: somesqlserver (the name used in the FreeTDS config file)
Database: jobfutures (change to suit)

Test the ODBC connection

Firstly we test using the iodbctest tool:

$ iodbctest "dsn=somesqlserver;uid=USERNAME;pwd=PASSWORD" 
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0001.0928
Driver: 0.64 (libtdsodbc.so)

SQL>use jobfutures;
1: SQLExec = [FreeTDS][SQL Server]Changed database context to 'jobfutures'. (5701) SQLSTATE=01000
Statement executed. 0 rows affected.

woohoo!

And now in the colourful world of Ruby using irb:

require 'rubygems'
#=> true
require 'dbi'
#=> true
DBI.connect('DBI:ODBC:somesqlserver', 'USERNAME', 'PASSWORD')
#=> #<DBI::DatabaseHandle:0x106cf74 @trace_output=#<IO:0x2b890>, @trace_mode=2, @handle=#<DBI::DBD::ODBC::Database:0x106ced4 @handle=#<ODBC::Database:0x106cefc>, @attr={}>>

Woohoo! Only one tiny step between us and world SQLServer domination.

Configure Rails

All that’s left is to modify our database.yml to refer to our shiny new ODBC DSN:

development:
  adapter: sqlserver
  mode: odbc
  dsn: someservername
  username: USERNAME
  password: PASSWORD

Load up script/console and give it a whirl.

phew! If you made it this far you deserve a break, a beer and a few days off work (file a claim for “MS torture leave”).