odbc unable to connect to MSSQL data source on OS X Lion

Question

odbc isql was unable to connect to data source:

 $ isql SMS_GTWY username password -v

returns:

 [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
 [01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
 [ISQL]ERROR: Could not SQLConnect

while tsql is ok:

 $ tsql -S SERVER001 -U username -P password

returns:

 locale is "C/UTF-8/C/C/C/C"
 locale charset is "UTF-8"
 using default charset "UTF8"
 1> 

$ cat ~/.freetds.conf:

 [SERVER001]
   host = 192.168.8.101
   port = 1433
   tds version = 8.0
   client charset = UTF8

$ cat ~/.odbc.ini:

  [SMS_GTWY]
  Description = SERVER001 Server
  Driver      = freetds
  Database    = SMS_GTWY
  ServerName  = SERVER001
  TDS_Version = 7.1

$ cat ~/.odbcinst.ini:

  [freetds]
  Description = MS SQL database access with FreeTDS
  Driver      = /usr/local/lib/libtdsodbc.so
  Setup       = /usr/local/lib/libtdsodbc.so
  UsageCount  = 1

freetds and unixODBC are install by homebrew.

I have a similar setup on CentOS and odbc isql to mssqlserver is working fine. There is a noticable difference, I do not see a libtdsS.so installed on OS X.

# cat /etc/odbcinst.ini on CentOS:

 [freetds]
 Description = MS SQL database access with FreeTDS
 Driver      = /usr/lib/libtdsodbc.so
 Setup       = /usr/lib/libtdsS.so 
 UsageCount  = 1

Is that the cause of problem?

p.s. $ odbcinst -j returns:

 unixODBC 2.3.1
 DRIVERS............: /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini
 SYSTEM DATA SOURCES: /usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini
 FILE DATA SOURCES..: /usr/local/Cellar/unixodbc/2.3.1/etc/ODBCDataSources
 USER DATA SOURCES..: /Users/horace/.odbc.ini
 SQLULEN Size.......: 8
 SQLLEN Size........: 8
 SQLSETPOSIROW Size.: 8

$ odbcinst -q -d returns:

 [freetds]

$ odbcinst -q -s returns:

 [SMS_GTWY]

Ammonate

Answer 1

isql works after reinstall unixodbc and freetds ( --with-unixodbc ):

 brew uninstall freetds
 brew uninstall unixodbc

 brew install unixodbc
 brew install freetds --with-unixodbc

Now, $ isql -v SMS_GTWY username password returns:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

Credit: https://gist.github.com/565440

Ammonate

You are a genius! This saved my bacon while attempting to follow these otherwise useful instructions. - acloudtree.com/… – Thorazine

Thank you. I also had this problems. Reinstalling – Rucksack

Answer 2

Ok. I wasted two days on this as well but did not want to use Homebrew or MacPorts. The trick is to build FreeTDS with the the unixODBC switch. This generates the odbc driver (xxx.so) which is not generated otherwise. To start you will need XCode and the Command Line Tools already installed. Plenty of resources on the web to show how to do that.

Here are the steps I took.

1. Download Latest Stable FreeTDS
2. Download Latest Stable unixODBC
3. Unpack both.
4. Note the full path to unpacked unixODBC directory.

5. From a terminal prompt navigate in to the FreeTDS directory and issue the following commands.

   ./configure --with-unixodbc=noted path in Step 4
   make
   sudo make install

When complete, everything should be in /usr/local/. Your driver should be in /usr/local/lib. Edit your settings files in /usr/local/etc. Hope this helps.

Piet

Answer 3

For anyone who use MacPort, the only different part from above is installing FreeTDS this way:

sudo port install freetds +mssql +odbc +universal

and the driver library will be here:

/opt/local/lib/libtdsodbc.so

Manny