Search code examples
sql-servernode.jsunixodbc

Node-odbc unable to connect to mssql

I've spent the last day or two setting up unixODBC and freetds on ubuntu 12 - not a fun process in itself but it does now work using both sqsh and isql. I've installed node-odbc and I'm using the code snippet provided in the github readme to test the connection but I always get

 S1000:1:0:[unixODBC][FreeTDS][SQL Server]Unable to connect to data source
 WARNING: ev_unref is deprecated, use uv_unref
 [Error: Error opening database]

Using isql I run isql -v SERVER user pass (using the correct creds..) and then use DATABASE once connected and it all works and I can run queries fine. My connection string in the js is 

 "DRIVER={FreeTDS};SERVER=SERVER;UID=user;PWD=pass;DATABASE=DATABASE"

Which exactly matches the credentials used for connecting with isql but in Node I get the aforementioned error. Any ideas on why this is happening? Is it possible that it's to do the location of my odbc.ini and odbcinst.ini files or something like that?

Just for reference:

/etc/odbc.ini:

[SERVER]
Driver      = FreeTDS
Trace       = No
Server      = SERVER
Port        = 1433
Database    = DATABASE
UsageCount  = 1
TDS_Version = 7.0

/etc/odbcinst.ini:

[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage   = 1
CPTimeout   = 5
CPReuse     = 20
Threading   = 1

Thanks in advance for any help!

Solution

  • It looks like I have just found a solution to this although I don't fully understand why. If the connection string used is:

    "DRIVER={FreeTDS};SERVER=SERVER;UID=user;PWD=pass;DATABASE=DATABASE"

    Then for some reason it doesn't work at all, I've tried using setting SERVER as both the IP and the actual name of the machine...no luck. However if I change the connection string to use SERVERNAME or DSN (can only have one of SERVER, SERVERNAME or DNS in the string) and I supply the machine name then it works fine, I can't get it to work with IP no matter what I try though. So, in summary the connection string that is working for me is:

    "DRIVER={FreeTDS};SERVERNAME=SERVERNAME;UID=user;PWD=pass;DATABASE=DATABASE"

    The key to this problem seems to lie in SQLDriverConnect.