13

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.

Ben Scheirman
  • 40,531
  • 21
  • 102
  • 137
  • 1
    `I can verify that my server is online and the port is open (via telnet & yougetsignal.com port check).` Sorry, I can't answer your question. However you should know that exposing SQL Server to the internet is likely to lead to a security problem. Various bots scan for SQL Server and will try to connect/attack it. –  Jan 13 '11 at 05:32
  • Do you have to use MRI? Can't you use JRuby and JDBC? – Luke Jan 13 '11 at 07:09
  • @will - Thanks for pointing that out. I am very aware of how bad that is, but in the production system I'll be locking it down by IP address and changing the port. I'm just trying to validate that all these pieces work together. – Ben Scheirman Jan 13 '11 at 13:22
  • I have zero experience with JDBC or the JVM, but I'd say it's _possible_, just not likely that I'd choose it. – Ben Scheirman Jan 13 '11 at 13:23

1 Answers1

14

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

Klaus
  • 156
  • 1
  • 2