Roland's blog

To connect to the Sql Server in from Rails in Linux there are some very useful resources such as:
HowtoConnectToMicrosoftSQLServerFromRailsOnLinux.

Note that HowtoConnectToMicrosoftSQLServer works only if the Rails app lives in a windows environment. In Linux there's no "win32ole", which helps with the ADO connection. So, we have to rely on ODBC. The weird thing is that I developed a rails app that connected to Sql Server 2005 a few years back, but we did it in windows and had not to worry about something like this. Oh well, c'est la vie!

On my laptop I run Arch Linux so these instructions are pretty much for Arch, but as the first resource says, they can be adapted to pretty much any other distro. First, start off by installing freetds. Luckily, freetds on Arch is built with odbc support (you can see that the PKGBUILD of freetds depends on unixodbc). In other distros you'd have to install unixodbc before freetds.

After installing FreeTDS install rubyodbc from http://www.ch-werner.de/rubyodbc/ and ruby-dbi by:

sudo gem install dbi

PS: The ADO.rb instruction is only for Windows.

sudo pacman -S freetds

Next configure FreeTDS

sudo vim /etc/freetds/freetds.conf

Add:

[ContractsDB]
  host = db.domain.com
  port = 1234
  tds version = 8.0

Next define the freetds driver in odbc by editing

sudo vim /etc/odbcinst.ini

Add:

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

Finally, tell odbc to use the "FreeTDS" driver you secified above:

sudo vim /etc/odbc.ini

[ContractsDB]
Driver = FreeTDS
Description = ODBC Connection via FreeTDS
Trace = No
Servername = ContractsDB
Database = Contracts

Make sure that the naming "ContractsDB" (or something else) is preserved throughout your configs. I changed the "ContractsDB" in freetds.conf to something else and updated the odbc.ini's Servername, but I wasn't able to connect.

Testing

To test that freetds can connect do

tsql -S db.domain.com -U username -P password

To test that ODBC can connect do

isql db.domain.com username password

Now you can connect to the DB. The funky thing is that for some reason Rails keeps saying "Table doesn't exist", but is able to query the database just fine. This doesn't make any difference, but it screws up scaffolding. After some investigating, it turns out that "table_exists?" is not defined in the adaptor. You can define the method there and return true, to shut it up, but scaffolding will still be screwed up.

The weird thing is that Rails appears to have changed their logging because none of the sql goes in it, or it so appears to be.