UnixODBC/FreeTDS -- dead in the water

I've been developing on a legacy (SQLServer DB) using one-click. The client is excited. In fact, the boss is requiring daily updates as to how I'm doing on deployment. Not so good.

I've followed http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux, and everything goes fine until I attempt the isql command. The command fails. The server trace complains that the login packet is corrupt.

My Setup: RHEL 5. (unixODBC preinstalled, although compiling from scratch did not help) freeTDS 0.64 d/led & installed.

/etc/odbc.ini: [EIDDEVSQL] Driver = FreeTDS Description = ODBC via FreeTDS Trace = Yes TraceFile = /tmp/odbc_tr Server = 10.236.9.88 Host = 10.236.9.88 Database = CapacityPlannerDBO Port = 1433

/etc/odbcinst.ini: [FreeTDS] Description = v0.64 with protocol v8.0 Driver = /usr/local/lib/libtdsodbc.so UsageCount = 2

[ODBC] Trace = Yes TraceFile = /tmp/odbcinst_tr ForceTrace = Yes

/usr/local/etc/freetds.conf: ... [EIDDEVSQL]         host = eiddevsql.amd.com         port = 1433         tds version = 8.0         client charset = UTF-8

Note that tsql connects just fine.

bash-3.1# isql -v eiddevsql USER PASSWORD [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source [28000][unixODBC][FreeTDS][SQL Server]Login incorrect. [08S01][unixODBC][FreeTDS][SQL Server]Read from SQL server failed. [ISQL]ERROR: Could not SQLConnect

bash-3.1# cat /tmp/odbcinst_tr [ODBC][29991][__handles.c][444]                 Exit:[SQL_SUCCESS]                         Environment = 0x969eb58 [ODBC][29991][SQLAllocHandle.c][345]                 Entry:                         Handle Type = 2                         Input Handle = 0x969eb58 [ODBC][29991][SQLAllocHandle.c][463]                 Exit:[SQL_SUCCESS]                         Output Handle = 0x969f130 [ODBC][29991][SQLConnect.c][3549]                 Entry:                         Connection = 0x969f130                         Server Name = [eiddevsql][length = 9 (SQL_NTS)]                         User Name = [USERNAME][length = 18 (SQL_NTS)]                         Authentication = [*******][length = 7 (SQL_NTS)]                 UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

                DIAG [08S01] [FreeTDS][SQL Server]Read from SQL server failed.

                DIAG [28000] [FreeTDS][SQL Server]Login incorrect.

                DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source

[ODBC][29991][SQLConnect.c][3917]                 Exit:[SQL_ERROR] [ODBC][29991][SQLError.c][424]                 Entry:                         Connection = 0x969f130                         SQLState = 0xbfe1576e                         Native = 0xbfe15570                         Message Text = 0xbfe15579                         Buffer Length = 500                         Text Len Ptr = 0xbfe15576 [ODBC][29991][SQLError.c][461]                 Exit:[SQL_SUCCESS]                         SQLState = S1000                         Native = 0xbfe15570 -> 0                         Message Text = [[unixODBC][FreeTDS][SQL Server]Unable to connect to data source] [ODBC][29991][SQLError.c][424]                 Entry:                         Connection = 0x969f130                         SQLState = 0xbfe1576e                         Native = 0xbfe15570                         Message Text = 0xbfe15579                         Buffer Length = 500                         Text Len Ptr = 0xbfe15576 [ODBC][29991][SQLError.c][461]                 Exit:[SQL_SUCCESS]                         SQLState = 28000                         Native = 0xbfe15570 -> 20014                         Message Text = [[unixODBC][FreeTDS][SQL Server]Login incorrect.] [ODBC][29991][SQLError.c][424]                 Entry:                         Connection = 0x969f130                         SQLState = 0xbfe1576e                         Native = 0xbfe15570                         Message Text = 0xbfe15579                         Buffer Length = 500                         Text Len Ptr = 0xbfe15576 [ODBC][29991][SQLError.c][461]                 Exit:[SQL_SUCCESS]                         SQLState = 08S01                         Native = 0xbfe15570 -> 20004                         Message Text = [[unixODBC][FreeTDS][SQL Server]Read from SQL server failed.] [ODBC][29991][SQLError.c][424]                 Entry:                         Connection = 0x969f130                         SQLState = 0xbfe1576e                         Native = 0xbfe15570                         Message Text = 0xbfe15579                         Buffer Length = 500                         Text Len Ptr = 0xbfe15576 [ODBC][29991][SQLError.c][461]                 Exit:[SQL_NO_DATA] [ODBC][29991][SQLError.c][504]                 Entry:                         Environment = 0x969eb58                         SQLState = 0xbfe1576e                         Native = 0xbfe15570                         Message Text = 0xbfe15579                         Buffer Length = 500                         Text Len Ptr = 0xbfe15576 [ODBC][29991][SQLError.c][541]                 Exit:[SQL_NO_DATA] [ODBC][29991][SQLFreeHandle.c][268]                 Entry:                         Handle Type = 2                         Input Handle = 0x969f130 [ODBC][29991][SQLFreeHandle.c][317]                 Exit:[SQL_SUCCESS] [ODBC][29991][SQLFreeHandle.c][203]                 Entry:                         Handle Type = 1                         Input Handle = 0x969eb58

This configuration seems to be more in-line with how I have to get things working:

/etc/odbc.ini: [EIDDEVSQL] Driver = FreeTDS Description = ODBC via FreeTDS Trace = Yes TraceFile = /tmp/odbc_tr Servername = EIDDEVSQL_TDS << Database = CapacityPlannerDBO Port = 1433

Also, don't use Host, that is what the FreeTDS configuration of for

/usr/local/etc/freetds.conf: ... [EIDDEVSQL_TDS]         host = eiddevsql.amd.com         port = 1433         tds version = 8.0         client charset = UTF-8

This looks like it should work. We are also building Rails apps with a legacy MS SQL DB (ugh) and our configuration is more like what I have listed above. It would really be nice if we could go straight to FreeTDS without having to add the ODBC layer, but the ruby-FreeTDS project is only a partial solution.

AMAZING!

Thanks.