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:
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.
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