Ruby connecting to SQL Server

Ben Scheirman picture Ben Scheirman · Jan 13, 2011 · Viewed 19.5k times · Source

I'm having troubles connecting Ruby to Microsoft SQL Server. I'm running Mac OS X, but the target environment is Ubuntu Linux.

Here's what I've tried:

  • Install unixODBC
  • Install FreeTDS
    • used the options --with-unixodbc=/usr/local/etc --with-tdsver=8.0

I then had these files in /usr/local/etc:

  • odbc.ini
  • odbcinst.ini
  • freetds.conf

I added a reference to the FreeTDS driver in the odbcinst.ini file to my ODBC driver file like this:

;
; odbcinst.ini
;
;
[FreeTDS]
Driver = /usr/local/lib/libtdsodbc.so

Then I configured the server in the freetds.conf file like this:

# Aries database server (SQL Server 2008)
[aries-db1]
        host = xx.xx.xx.xx
        port = 1433
        tds version = 8.0

And finally I added the ODBC DSN in the odbc.ini file like this:

[aries-db1]
Driver      =   FreeTDS
Description =   ODBC Connection via FreeTDS
Trace       =   1
Servername  =   aries-db1
Database    =   MY_DB
UID         =   user1
PWD         =   pass1

I can verify that my server is online and the port is open (via telnet & yougetsignal.com port check).

As a test, I did this:

tsql -S aries-db1 -U user1 -P pass1

And it seemed to connect just fine. Passing in invalid values resulted in expected errors.

So finally to my question:

How do I extend this to Ruby? Nothing I've tried so far has worked. I tried Sequel like this:

require 'sequel'
Sequel.connect('aries-db1')['select * from foo'].all

And I get an error like this:

Sequel::DatabaseConnectionError: ODBC::Error: S1000 (0) [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Which tells me it is finding my driver configuration correctly, but for some reason cannot connect.

I also tried DBI like this:

DBI.connect('DBI:ODBC:aries-db1')

And I get a similar error.

Any suggestions? I feel like I'm very close, but am not sure what to try next to troubleshoot this.

Answer

Klaus picture Klaus · Jan 13, 2011

Have a Look at https://github.com/rails-sqlserver/tiny_tds TinyTds - A modern, simple and fast FreeTDS library for Ruby using DB-Library

Its easy to install and easy to use