Connecting to MS SQL Server 2005 from Mac using Ruby, ODBC and FreeTDS

Question

I'm having trouble connecting to a SQL Server 2005 install from this very simple Ruby app:

#!/usr/bin/env ruby
require 'rubygems'
require 'sequel'

Sequel.odbc('dev04')['select top 1 * from users'].all

The dev04 DSN points to my odbc.ini file entry:

[dev04]
Driver      = FreeTDS
Description = ODBC connection via FreeTDS
Trace       = 1
Servername  = dev04
Database    = Dev04
UID         = uDev04
PWD         = pwdDev04

FreeTDS is installed in odbcinst.ini and it points to this configuration in freetds.conf:

[dev04]
host = hm602.mycompany.com
port = 1433
tds version = 7.0

This works:

carlos$ tsql -S dev04 -U uDev04 -P pwdDev04
locale is "en_GB.UTF-8"
locale charset is "UTF-8"
1> exit
carlos$

And so does this:

carlos$ iodbctest "DSN=dev04;UID=uDev04;PWD=pwdDev04"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0406.1211
Driver: 0.82 (libtdsodbc.so)

SQL>exit 

Have a nice day.
carlos$

When I run that Ruby script, though, I get this error:

carlos$ ruby mssql.rb 
/Library/Ruby/Gems/1.8/gems/sequel-3.4.0/lib/sequel/adapters/odbc.rb:37:in `initialize': ODBC::Error 01000 (20002) [FreeTDS][SQL Server]Adaptive Server connection failed (Sequel::DatabaseConnectionError)
    from /Library/Ruby/Gems/1.8/gems/sequel-3.4.0/lib/sequel/adapters/odbc.rb:37:in `connect'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.4.0/lib/sequel/adapters/odbc.rb:37:in `connect'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.4.0/lib/sequel/database.rb:92:in `initialize'
...

I understand the 'Adaptive Server connection failed' error can occur due to TDS protocol version discrepancies. I've checked the logs and they all seem to be using the correct version (7.0), so I'm at a complete loss as to why this could be happening.

Any pointers?

Answer 1

I'm the Sequel maintainer. Unfortunately, I don't have any personal experience using Sequel to connect to MSSQL using FreeTDS. I know other people have used it successfully though.

Your error message indicates a issue in the underlying Ruby/ODBC library, not in Sequel itself. If you can get a connection working using Ruby/ODBC directly, it shouldn't be hard to get it working with Sequel.

Sorry I can't be more help.

Also, while it is unrelated to your current issue, you should use the :db_type=>'mssql' option to the Sequel.odbc call.

Answer 2

Sometimes, free isn't without cost.

TDS Version 7.0 was correct for SQL Server 7.x, and it will connect in many cases to later versions of SQL Server -- but it does not support all features or functionality of those later versions.

TDS Version 7.1 is correct for SQL Server 2000 (v8.x); 7.2 is correct for SQL Server 2005 (v9.x); 7.3 is correct for SQL Server 2008 (v10.x). Due to some confusion in Microsoft's documentation, these are usually specified in the freetds.conf using the SQL Server version, not the TDS version; and SQL Server 2008 isn't fully supported yet; so I'd test with

tds version = 7.1
tds version = 7.2
tds version = 8.0
tds version = 9.0

Or ... you could try using a commercial driver that has already handled much of the head-scratching. If you do go that route, I'd suggest you also review the full documentation for the Rails ODBC Adapter