Monday, March 19, 2012

Connect Rails 3.1 From Mac OS X to SQL Server

I’m currently using Ruby on Rails 3.1 and am developing on a Mac.  A requirement was to use MS SQL Server as the database.  There are a couple hoops to jump through to get RoR to work with SQL Server.  Fortunately, the Tiny TDS gem made this a breeze to setup without having to go through the headache of unixodbc/iodbc/rubyodbc configuration.

Here’s the following steps to accomplish:

1.  Install FreeTDS using brew

brew install freetds

2.  Add the following gems to the Gemfile

  gem 'activerecord-sqlserver-adapter'
  gem 'tiny_tds'

3.  Run bundle install to update the gems

4.  Modify the database.yml file to the following:

    adapter: sqlserver
    mode: dblib
    dataserver:   <IP Address>\<Instance Name>
    database: database_name
    username: sql_user
    password: sql_password

Things of note:

1.  Additional ways to configure the connection string can be found here

2.  If you’re using a named instance of SQL, it’s not going to use the default 1433 port.  Through SQL Management Studio, you can run the following command to find out what port your instance is running on:

use master


If this doesn’t initially work, there are a couple potential points of failure.  Here are some of the debugging techniques I used to verify I could connect to SQL Server without rails:

1.  Verify that you can make a connection to SQL Server.  From terminal prompt, do the following:

telnet <IP Address> <port>

If you connect, you'll receive the following:

Connected to
Escape character is '^]'.

2.  Once you’ve verified that you can connect using telnet, second step is to verify that you can connect via FreeTDS.  From the terminal prompt, do the following:

tSQL –H <IP Address> –p <Port> –U sa -P password

If connected properly, you should see the following prompt:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"

At this point, this is a SQL editor so you could use SQL syntax to extract queries.  For our purposes, we just want to verify that we can connect this way. 

Type exit to exit out of application

3.  I also ran into an issue when connecting using Rails I received the following error “Adaptive Server timeout”.  I resolved this by doing another bundle install and it has worked after that.

Happy Developing!