Odbc cannot connect to MSSQL data source on OS X Lion

Question

odbc isql could not connect to the 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 fine:

  $ 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 installed on the brew house.

I have a similar installation on CentOS, and odbc isql on mssqlserver works fine. There is a noticeable difference; I do not see 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 this the cause of the problem?

ps $ 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] 

Answer 1

isql works after reinstalling 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

Answer 2

Ok I spent two days on this, but did not want to use Homebrew or MacPorts. The trick is to create FreeTDS using the unixODBC switch. This generates the odbc driver (xxx.so), which is not generated otherwise. First you need Xcode and the command line tools are already installed. Many resources on the Internet to show how to do this.

Here are the steps I took.

• Download Latest Stable FreeTDS
• Download the latest stable unixODBC
• Unzip both.
• Pay attention to the full path to the unixODBC unpacked directory.

• At the terminal command prompt, change to the FreeTDS directory and run the following commands.

  ./set up - c-unixodbc = marked path in step 4
  to do
  sudo make install

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

Answer 3

For those using MacPort, the only other part on top is installing FreeTDS this way:

 sudo port install freetds +mssql +odbc +universal 

and the driver library will be here:

 /opt/local/lib/libtdsodbc.so