In a recent opensteam project, we had to work with a legacy database on a MS SQL Server 2005.
Well, connecting rails to a mssql server can be a pain. But after some research we made it work on a windows-client (using the ADO driver) and on a linux (ubuntu) client (using unixODBC and freetds), by following the instructions at http://piao-tech.blogspot.com/2008/02/using-activerecord-with-microsoft-sql.htm

Yesterday i wanted to test the connection on my mac and installed unixodbc and freetds via MacPorts, but i always got a "Unexpected EOF from the server" error, when testing the connection-settings with tsql.

FreeTDS

Turns out, the solution is to install a freetds variant via MacPorts:

   1  $> sudo port install freetds +msql

which configures freetds with "—with-tdserv=8.0 —enable-msdblib".

And now the connection with tsql works:

   1  $> tsql -H hostname -U username -p port
   2  locale is "de_AT.UTF-8"
   3  locale charset is "UTF-8"
   4  Password:
   5  1> # we're in!!

iODBC

Now we have to configure ODBC with FreeTDS. Since OSX 10.5 comes pre-installed with iodbc, we make our life simple and use it.

Edit /Library/ODBC/odbcinst.ini: (mine looks like this)

   1  [ODBC Drivers]
   2  tds = Installed
   3  
   4  [tds]
   5  Driver = /opt/local/lib/libtdsodbc.so
   6  Setup  = /opt/local/lib/libtdsodbc.so

and /Library/ODBC/odbc.ini

   1  [ODBC]
   2  Trace = 0
   3  
   4  [A_DSN]
   5  Driver = TDS
   6  Description = ODBC connection via FreeTDS
   7  Trace = No
   8  Server = hostname_or_ip 
   9  Database = DATABASE_NAME
  10  Port = 1410

Here we're using a "freetds.conf"-less configuration and specify the server and port directly in the odbc.ini file. For more configuration settings see http://cubist.cs.washington.edu/doc/FreeTDS/userguide/x1853.htm.

Now we can test our settings with iodbctest

   1  $> iodbctest "dsn=A_DSN;uid=USERNAME;pwd=PASSWORD"
   2  iODBC Demonstration program
   3  This program shows an interactive SQL processor
   4  Driver Manager: 03.52.0406.1211
   5  Driver: 0.82 (libtdsodbc.so)
   6  
   7  SQL> # <= In Again!!

Rails + ActiveRecord

First install some gems:

   1  sudo gem install dbi dbd-odbc
   2    sudo gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com
   3    # or
   4    sudo gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org

Second, install the ruby-odbc bindings from http://www.ch-werner.de/rubyodbc/

   1  $> tar xvzf ruby-odbc-0.9995.tar.gz
   2  $> cd ruby-odbc-0.9995
   3  $> ruby extconf.rb
   4  $> make
   5  $> sudo make install

Third, fire up irb and test if everything is working:

   1  >> require 'dbi'
   2  => true
   3  >> DBI.connect('dbi:ODBC:A_DSN', 'USERNAME', 'PWD' )
   4  => #<DBI::DatabaseHandle:0x1200318...>

And now with ActiveRecord:

   1  >> require 'activerecord'
   2  => true
   3  >> h = { :dsn => 'A_DSN', :password => 'pwd', :username => 'username', :mode => 'odbc', :adapter => 'sqlserver' }
   4  >> ActiveRecord::Base.establish_connection(h)
   5  => #<ActiveRecord::ConnectionAdapters::ConnectionPool:0x183ad50 ..>
   6  
   7  >> ActiveRecord::Base.connection.tables
   8  => ["...", "..."] # should display your tables

And we have successfully connected Rails to a MS SQL Server!

Keep in mind to allow tcp/ip connections on your MS SQL Server (Configuration Tools) and allow remote access for your user on the server (Management Tools).