This document is an adaptation from HowtoConnectToMicrosoftSQLServerFromRailsOnLinux to FreeBSD. It basically indicates the port names to use and the correct path names. I have tested these config on FreeBSD 6.1
This document was built in hopes to make getting SQL Server talking to Rails on FreeBSD as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.

There are 4 variables i have captialized which should be self explanatory, but if not:

• YOUR_DB_DEFINITION_NAME – Pick something and stick with it. Used for FreeTDS and as your DSN in unixODBC. I used the same for both to keep it simple.
• YOUR_ACTUAL_DB_NAME – The actual name of the database you are trying to connect to
• USERNAME – The username for that database
• PASSWORD – The password for that database

Replace these in the places they show up below.

Assumed:

• Ruby 1.8.5 (/usr/ports/lang/ruby18)
• Rails 1.1.6 (/usr/ports/www/rubygem-rails)

Required:

• FreeTDS >= 0.63 (/usr/ports/databases/freetds)
• unixODBC >= 2.2.11 (/usr/ports/databases/unixODBC)
• Ruby DBI >= 0.0.23 (/usr/ports/databases/ruby-dbi)
• Ruby ODBC >= 0.996 (/usr/ports/databases/ruby-dbd_odbc)

First things first

Become root

FreeTDS:

cd /usr/ports/databases/freetds && make install
# or
portinstall databases/freetds

depending on your version of FreeBSD, you might also have to do

cd /usr/ports/databases/freetds-msdblib && make install
# or
portinstall databases/freetds-msdblib

edit FreeTDS config file

cp /usr/local/etc/freetds.conf.dist /usr/local/etc/freetds.conf
# vi /usr/local/etc/freetds.conf

Add a section to the bottom of the file:

[YOUR_DB_DEFINITION_NAME]
        host = 192.168.1.101  (change this to the right one for you)
        port = 1433
        tds version = 4.2  (might be 8.0, check the docs for the version 
                                     of SQL Server you are running)

# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

You should see and do the following to make sure all went well:

locale is "en_US.ISO-8859-15" 
locale charset is "ISO-8859-15" 
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users   (do this for a table that exists in your db)
2> go
id      login
1       user1
3       user2   (make sure you get correct output)
1> quit

If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…

unixODBC:

I installed this via apt-get, so I dont have command line instructions and you are on your own for this one.

cd /usr/ports/databases/unixODBC && make install
# or
portinstall databases/unixODBC

After you’ve got it installed:

/usr/local/etc/odbc.ini:

[YOUR_DB_DEFINITION_NAME]
Driver          = TDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = YOUR_DB_DEFINITION_NAME
Database        = YOUR_ACTUAL_DB_NAME

/usr/local/etc/odbcinst.ini:

[TDS]
Description     = FreeTDS
Driver          = /usr/local/lib/libtdsodbc.so
UsageCount      = 1

Try it out:

# isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from users;    (again, do this for a table that exists in your db)
+------------+-------------------------------+
| id         | login                         |
+------------+-------------------------------+
| 1          | user1                         |
| 3          | user2                         |
+------------+-------------------------------+
2 rows affected
2 rows returned
SQL> quit

NOTE: If you have problems connecting read my notice at the end of this page

If all went well, move on…

Ruby ODBC:

cd /usr/ports/databases/ruby-odbc && make install
# or
portinstall databases/ruby-odbc

Ruby DBI ODBC driver:
NOTE: make sure not to confuse ruby-dbd_odbc with ruby-odbc

cd /usr/ports/databases/ruby-dbd_odbc && make install
# or
portinstall databases/ruby-dbd_odbc

Test everything:

Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:

# irb1.8
irb(main):001:0> require "dbi" 
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2, 
    @handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={}, 
    @handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit

If everything went well, go make it work in Rails:

Setup Rails

database.yml:

development:
  adapter: sqlserver
  mode: odbc
  dsn: YOUR_DB_DEFINITION_NAME
  username: USERNAME
  password: PASSWORD

Add other sections in the same manner.

SQL Server working with Rails on FreeBSD!

Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.

Troubleshooting

If you get an error along the lines of ‘iODBCSpecified driver could not be loaded’, it might help to set the Driver value in /usr/local/etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence

My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
—GregoryBluvshteyn (2006-6-21)

In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
—Jeremie Blais (2006-7-18)

category: Howto