hughevans.net

Whilst I cannot highly recommend the use of MS SQL Server with Rails it may be necessary in some situations for legacy integration and even then you have to carefully consider it’s usage (I’ll have more to say on this topic soon). Anyway when I first started to look into it I was told that connecting to a SQL Server via ODBC on Linux was both difficult and unreliable, but I found it to be neither. I have the instructions listed below for Ubuntu Gutsy and it will probably work on Hardy as well:

Start by installing the required packages (assuming you already have a Ruby and Rails stack installed):

sudo apt-get install unixODBC libdbd-odbc-ruby tdsodbc freetds-dev sqsh

Edit your ~/.bashrc file (assuming that you are just using the current user for the ODBC connection) and add the following to the bottom:

export ODBCINI=/home/<USER>/.odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/home/<USER>/.freetds.conf

Create ~/.freetds.conf and add the following:

# Compliments /etc/freetds/freetds.conf 

[GLOBAL]
        text size = 64512

[<YOUR_CHOSEN_SERVER_NAME>]
        host = <SQL_SERVER_ADDRESS>
        port = 1433
        tds version = 8.0
        nt domain = <NT_DOMAIN>
        try server login = no
        try domain login = yes

You can then test it out with sqsh:

sqsh -S <YOUR_CHOSEN_SERVER_NAME> -U "<NT_DOMAIN>\<USER>" 

Now create ~/.odbc.ini and add the following:

[<DSN_NAME>]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = <YOUR_CHOSEN_SERVER_NAME>
Database        = <DEFAULT_DB>

Lastly we sudo edit /etc/odbcinst.ini and add the following:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

Thats it but for one last tip, as of Rails 2 the SQL Server adapter is not included by default so I like to put it in my Rails vendor/plugins/adapters/sqlserver dir and that just works.