Rails and Microsoft Sql Server

So you want to connect your rails application to Microsoft SQL Server. The following instructions will take you through setting this up quite simply. This setup is on a a Ubuntu 8.04 server connecting to SQL Server 2000 (we will use the Northwind sample database). The first thing you need to do is install the following gems:

$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4
$ gem install rails-sqlserver-2000-2005-adapter -s https://gems.github.com

More info can be found on the sqlserver adapter gem development page. This is just the first step.

Now modify your ~/.profile to include the following environment settings:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf

Reload by logging out then back in.

We need to install some packages, including FreeTDS, unixodbc, and the ruby odbc library:

$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc libdbd-odbc-ruby

Configure the newly installed FreeTDS with the settings for your MS Sqlserver:

$ nano /etc/freetsd/freetsd.conf

Add the following, make sure to use tabs (‘–tab–‘), not spaces for the settings:

[MSSQLSERVER]
'--tab--'  host = 1.2.3.4 (IP ADDRESS OR FQDN)
'--tab--'  port = 1433
'--tab--'  tds version = 7.0

Save and exit. Note we used tds version 7.0, other tds versions are here.




Test your connection using sqsh, ‘skwish’:

$ sqsh -S MSSQLSERVER -U username -P password
sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use Northwind
2> go
1> select top 1 CompanyName from Suppliers
2> go

[record returned]

(1 row affected)
1> quit

The next step is to configure the ODBC, there are a few ways but this worked for us:

$ nano /etc/odbcinst.ini

Input 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

Now configure the odbc.ini

$ nano /etc/odbc.ini

We used the following:

[NORTHWIND]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = MSSQLSERVER
Database        = Northwind

Another test now before the final test, make sure you can connect to the database and run a query. First connect:

$ isql -v NORTHWIND username password

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> use Northwind
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Northwind'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 CompanyName from Suppliers;

[record returned]

SQLRowCount returns 1
1 rows fetched
SQL> quit

Finally, lets make sure ruby can connect, launch irb, once launched type the following:

irb(main):001:0> require "dbi"
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:NORTHWIND', 'username', 'password')
=> #, @attr={}>, @trace_output=#,
@trace_mode=2>
irb(main):003:0> quit

A more complete test can be run by running a small script (albeit just a select). Create an rb file and add the following code:

#!/usr/bin/env ruby

require 'dbi'
db = DBI.connect('dbi:ODBC:NORTHWIND', 'username', 'password')
select = db.prepare('SELECT CompanyName FROM Suppliers')
select.execute
while rec = select.fetch do
  puts rec.to_s
end
db.disconnect

Save that file then execute it with ruby. Hopefully all is well.

Modify your database.yml to connect to the Northwind sqlserver database and you will be all set:

adapter:sqlserver
mode:odbc
dsn: NORTHWIND
username: user
password: pass

If everything seems to be failing to connect, make sure you can see your sqlserver and that you can connect. An easy way to test this is to telnet to the server or even just ping it:

$ telnet IPADDRESS(or FQDN) PORT

$ ping IPADDRESS (or FQDN)

Your Ruby application should now have no problem connecting to the Microsoft SQL Server