How To: Install FreeTDS and UnixODBC On OSX Using Homebrew For Use With Ruby, Php, And Perl

This little project started out as a basic script to connect to a Microsoft SqlServer and get data. It was a nightmare as I probably spent 15 hours learning about and troubleshooting both FreeTDS and UnixODBC. My pain is now your gain.

NOTICE: I have homebrew configured to install all packages into my local directory /Users/jared/.homebrew/

Install UnixODBC Link to heading

$ brew install unixodbc
==> Downloading http://www.unixodbc.org/unixODBC-2.3.0.tar.gz
File already downloaded in /Users/jared/Library/Caches/Homebrew
==> ./configure --disable-debug --prefix=/Users/jared/.homebrew/Cellar/unixodbc/2.3.0 --enable-gui=no
==> make install
/Users/jared/.homebrew/Cellar/unixodbc/2.3.0: 24 files, 932K, built in 22 seconds
$

Edit the FreeTDS formula And install Link to heading

What we are doing is changing the default tds version, enabling the msdblib, and pointing out where unixodbc installed.

require 'formula'
 
class Freetds > Formula
        url 'http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.91.tar.gz'
        homepage 'http://www.freetds.org/'
        md5 'b14db5823980a32f0643d1a84d3ec3ad'
 
        def install
                system "./configure",
                "--prefix=#{prefix}",
                "--with-tdsver=7.0",
                "--enable-msdblib",
                "--with-unixodbc=/Users/USERNAME/.homebrew/Cellar/unixodbc/2.3.0",
                "--mandir=#{man}"
                system 'make'
                ENV.j1 # Or fails to install on multi-core machines
                system 'make install'
        end
end

Start a new terminal session to make sure all your paths update.

Confirm that you can connect to the server Link to heading

We need to make sure that you can connect to the sqlserver and that the port is open and available to you.

To do this we use telnet. If you see the following, success! The port is open on the server.

$ telnet server.example.com 1433
Trying 192.168.1.101...
Connected to server.example.com.
Escape character is '^]'.

If you see the following. You failed. Check the Sqlserver configuration, firewalls, or network configuration.

$ telnet server.example.com 1433
Trying 192.168.1.101...
telnet: connect to address 192.168.1.101: Connection refused
telnet: Unable to connect to remote host

Note: Press the ctrl + ] keys to break to a prompt and then type exit.

Tsql Link to heading

FreeTDS comes with a couple cli applications. One of them is tsql. It isn’t great, but I use it test and see if at least FreeTDS is working correctly. After you install FreeTDS using homebrew try and connect to the host using the following command.

$ tsql -H server.example.com -U USERNAME -P PASSWORD -v
 
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>exit

If you see a prompt, you haz awesome!

Sym link the FreeTDS and UnixODBC conf files Link to heading

I create 3 sym links to the following files just for simplicity.

ln -s /Users/jared/.homebrew/Cellar/freetds/0.91/etc/freetds.conf ~/.freetds.conf
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbc.ini ~/.odbc.ini
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbcinst.ini ~/.odbcinst.ini

Edit the .freetds.conf and add the following Link to heading

[example]
host = server.example.com
port = 1433
tds version = 7.0

Edit the odbcinst.ini and add the following Link to heading

You are telling unixodbc where your FreeTDS drivers are located using this configuration file.

[FreeTDS]
Description = FreeTDS
Driver = /Users/jared/.homebrew/lib/libtdsodbc.so
Setup = /Users/jared/.homebrew/lib/libtdsodbc.so
UsageCount = 1

Edit the .odbc.ini and add the following Link to heading

[myexample]
Driver = FreeTDS // we just set this up a second ago
Description = MyExample
ServerName = example // this is the name of the configuration we used in the .freetds.conf file
UID = USERNAME
PWD = PASSWORD

Isql should work Link to heading

$ isql sqlinternal USERNAME PASSWORD
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL>quit

Osql Error Link to heading

If you try osql, it throws an error.

$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = MyExample
Servername = example
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is not an executable file
osql: error: no driver found for sqlinternal
$

If you go through the error you will find that a certain driver is not executable. You just need to chmod the file.

$ chmod 554 /Users/jared/.homebrew/Cellar/freetds/0.91/lib/libtdsodbc.0.so

Now run it again.

[jared@localhost]$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = myexamples
Servername = myexample
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is an executable file
Using ODBC-Combined strategy
DSN [myexample] has servername "myexample" (from /Users/jared/.odbc.ini)
/Users/jared/.freetds.conf is a readable file
looking for [myexample] in /Users/jared/.freetds.conf
found this section:
[myexample]
host = myexample.bendcable.net
port = 1433
tds version = 7.0
 
Configuration looks OK. Connection details:
 
DSN: myexample
odbc.ini: /Users/jared/.odbc.ini
Driver: /Users/jared/.homebrew/lib/libtdsodbc.so
Server hostname: myexample.bendcable.net
Address: 192.168.12.103
 
Attempting connection as username ...
+ isql myexample USERNAME PASSWORD -v
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL> quit

SUCCESS!!! Link to heading

Some other useful commands.

odbcinst -j
 
odbcinst -q -d
 
odbcinst -q -s