Connect To MicrosoftSQLServer From Rails On Linux Box

unixODBC >= 2.2.11 (http://www.unixodbc.org/)

unixodbc-dev

FreeTDS >= 0.63 (http://www.freetds.org/)

freetds-dev

Note that you need to install unixODBC before FreeTDS: The ./configure step of FreeTDS is dependent on unixODBC being installed: ./configure —with-unixodbc=/usr/local

Ruby ODBC binding (http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz). Extract and in the extracted folder run:

 ruby extconf.rb
 make
 sudo make install

 sudo gem install dbd-odbc
 sudo gem install dbi

If you are using SQL Server 2000, 2005, or 2008:

 sudo gem install activerecord-sqlserver-adapter

For more information, please see the rails adapterproject page

You don't have to mess with all the FreeTDS configuration files in the rest of this article if you use a DSN-less connection in your database.yml like the following:

 database_name:
   adapter: sqlserver
   mode: ODBC
   dsn: 'DRIVER=/usr/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=Your_Server_Name;DATABASE=Your_Database_Name;Port=1433;uid=Your_UID;pwd=Your_PWD;'

The obvious problem with this approach is having a different freetds installation location on development and production machines.

Make sure you set the Driver to the actual path of the your driver.

Add a section to /etc/freetds/freetds.conf

  [YOUR_DB_DEFINITION_NAME]
      host = <HOST NAME/IP ADDRESS>
      port = <PORT NUMBER>
      tds version = 8.0 
      client charset = UTF-8

Edit /etc/odbc.ini:

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

Edit /etc/odbcinst.ini:

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

tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

or

sqsh -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

  development:
    adapter: sqlserver
    mode: ODBC
    dsn: YOUR_DB_DEFINITION_NAME
    username:YOUR_DB_USERNAME
    password:YOUR_DB_PASSWORD

  test:
    adapter: sqlserver
    mode: ODBC
    dsn: YOUR_DB_DEFINITION_NAME
    username:YOUR_DB_USERNAME
    password:YOUR_DB_PASSWORD

  production:
    adapter: sqlserver
    mode: ODBC
    dsn: YOUR_DB_DEFINITION_NAME
    username:YOUR_DB_USERNAME
    password:YOUR_DB_PASSWORD