CONNECTING RAILS 3 TO SQL SERVER 2008 ON A MAC

Posted by John Lemp on Friday, January 21, 2011 · Leave A Comment 

I am writing this to document the steps I had to take to get a Rails 3 app to connect to an existing Sql 2008 Server using ActiveRecord. I have an existing database that the rails app needs to pull data from and I am developing locally on a mac. It is pieced together from several other HOWTO's and a bunch of trial and error.

The dependancies to make all of this work are unixodbc, FreeTDS, ruby-odbc and activerecord-sqlserver-adapter.

I use HomeBrew and luckily there are recipes for unixodbc and FreeTDS. So assuming you have HomeBrew already installed the first step is:

brew install unixodbc

the recipe for freetds required some tweaking so edit it with the following:

brew edit freetds

and add the follwing argument to ./configure

--with-unixodbc=/usr/local

and then install

brew install freetds

Now we will configure FreeTDS and unixodbc. The example assumes we are connecting to a Sql 2008 server with an IP address of 10.20.30.40 with a database named example_db using sql authentication with a username of example_login and password example_pass.

Create a user specific ~/.freetds.conf or edit /usr/local/etc/freetds.conf and add

[development_db]
host = 10.20.30.40
port = 1433
tds version = 8.0
client charset = UTF-8

Then configure unixodbc to use the FreeTDS driver by adding the following to  /usr/local/etc/odbcinst.ini :

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1

Now add an odbc data source in either  ~/.odbc.ini or /usr/local/etc/odbc.ini :

[development_db]
Driver= /usr/local/lib/libtdsodbc.so
Servername=development_db
Server=10.20.30.40
Port=1433
Database=example_db
TDS_Version=8.0

To test that FreeTDS is working you should be able to connect and run a test query using tsql:

tsql -H 10.20.30.40 -p 1433 -U example_login -P example_pass

If that does not work, verify that tsql -C shows

unixodbc: yes

Once that is verifed, you can test unixodbc by using isql :

isql development_db example_login example_pass

If that doesn't work, you can get some additional debug information using the following command:

TDSDUMP=stderr TDSVER=8.0 isql development_db example_login example_pass

Once FreeTDS and unixodbc are configured and working, we need two ruby gems added to the rails app, ruby-odbc and activerecord-sqlserver-adapter.  So add the following to your rails Gemfile:

gem "activerecord-sqlserver-adapter"
gem "ruby-odbc"

and do a bundle install.

Then alter the rails database.yml connection info with the following:

adapter : sqlserver
mode :  odbc
username : example_login
password : example_pass
database : example_db
dsn :  development_db

Your rails app should now be setup to connect to Sql Server.

Categories: All, Ruby on Rails · Tags: , , , , , ,

Leave A Comment