For the next project at work I have to interface Microsoft SqlServer 2008 from my rails applications. The setup is quite simple. First we need unixODBC. Normally Mac OS X ships with iODBC, but since I use linux servers, I wanted to use the layer that’s going to be used on my production systems aswell.
Lets start!
brew install unixodbc
Next, we need FreeTDS, which is a free implementation of the Tabular Data Stream protocol.
brew edit freetds
Add --with-unixodbc=/usr/local to the ./configure line. Then:
brew install freetds
Then I created ~~/.freetds.conf~ and added:
[development_db]
host = <Database IP>
port = 1433
tds version = 8.0
client charset = UTF-8
Edited /usr/local/etc/odbcinst.ini and added:
[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1
And ~~/.odbc.ini~
[development_db]
Driver= /usr/local/lib/libtdsodbc.so
Servername=development_db
Server=<Database IP>
Port=1433
Database=<database>
TDS_Version=8.0
To use the odbc connection, I just needed to install two ruby gems.
Just add the following to your Gemfile.
gem "activerecord-sqlserver-adapter"
gem "ruby-odbc"
I ran bundle install and configured my config/database.yml
adapter: sqlserver
mode: odbc
username: <login>
password: <password>
database: <database>
dsn: development_db