acloudtree

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/

1) Install UnixODBC

[jared@localhost]$ 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
[jared@localhost]$

2) Edit the FreeTDS formula And install

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
[jared@localhost]$ brew install freetds

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

4) Confirm that you can connect to the server

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.

[jared@localhost]$ 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.

[jared@localhost]$ 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.

5) Tsql

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.

[jared@localhost]$ 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!

6) Sym link the FreeTDS and UnixODBC conf files

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

7) edit the .freetds.conf and add the following

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

8 ) edit the odbcinst.ini and add the following

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

9) edit the .odbc.ini and add the following

[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

10) isql should work

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

11) Osql Error

If you try osql, it throws an error.

[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 = 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
[jared@localhost]$

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

[jared@localhost]$ 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!!!

Some other useful commands.

Useful commands

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

14 comments

  1. Lars says:

    Hi Jared,

    thank you very much for saving pain (!).
    I just made it till step 10, while isql says:

    [ISQL]ERROR: Could not SQLConnect

    So I went ahead to step 11. osql says:

    grep: /odbcinst.ini: No such file or directory

    I copied the file odbcinst.ini to my users directory as .odbcinst.ini.

    But what am I doing wrong?

    Sincerely
    Lars

  2. Hi Lars,

    What is the output from these commands?

    odbcinst -j

    odbcinst -q -d

    odbcinst -q -s

  3. Lars says:

    $ odbcinst -j
    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/lars/.odbc.ini
    SQLULEN Size…….: 8
    SQLLEN Size……..: 8
    SQLSETPOSIROW Size.: 8

    $ odbcinst -q -d
    [FreeTDS]

    $ odbcinst -q -s
    [myexample]

  4. And this?

    ls -alh ~/.odbc*

    Example:

    [jared@jared-mbp:~]$ ls -alh ~/.odbc*
    lrwxr-xr-x 1 jared staff 60B Jan 9 14:21 /Users/jared/.odbc.ini -> /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbc.ini
    lrwxr-xr-x 1 jared staff 64B Jan 9 14:21 /Users/jared/.odbcinst.ini -> /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbcinst.ini

  5. I think the difference here is that I install homebrew in my home directory. You look to be using /usr/local/

    You installation is looking here.

    DRIVERS…………: /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini

    So one solution (a hack) would be to edit that odbcinst.ini file directly.

  6. Lars says:

    $ ls -alh ~/.odbc*
    lrwxr-xr-x 1 lars staff 45B 18 Feb 20:22 /Users/lars/.odbc.ini -> /usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini
    lrwxr-xr-x 1 lars staff 49B 18 Feb 20:21 /Users/lars/.odbcinst.ini -> /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini
    Luftbuch:~ lars$

    Yep, after I found out to make simlinks (not copies), I deleted the copies and made simlinks the same way you did.

    the content of that file is (only):

    [FreeTDS]
    Description = FreeTDS
    Driver = /usr/local/lib/libtdsodbc.so
    Setup = /usr/local/lib/libtdsodbc.so
    UsageCount = 1

  7. So is the problem solved?

  8. Lars says:

    unfortunately, no … I still get the message, that odbcinst.ini is not found, hm … maybe there is an issue to the rights of the file or its directory? Just to chill myself, I do a restart right now (even if I know, that this won’t help :)

  9. Yup. Sounds like a permissions issue. And yeah, a restart is not going to do anything :-)

    Again, because I install homebrew in my home directory, my user easily has access to everything. Your user is trying to access libraries found in /usr/local which could potentially be a problem.

    You could always assume root with `sudo -i`, and try running tsql, isql, osql as root to see what happens. No guarantees though.

  10. Lars says:

    Yep, so I will repeat all that, after I did the same – installed homebrew to my home directory. Before I do that I’ll try it as root with simlinks to /var/root. I will let you know what happens.

  11. Lars says:

    Yeah … got it :)

    After I installed brew in home, osql still doesn’t found odbcinst.ini.
    However … I simlink(ed) it to / … quick and dirty :D
    (Dont’t know if it’s maybe also an 10.7.3 issue? I’ll have a look to that later)

    So for now … I can connect to my business SQL Server via a VPN connection (I bundled all traffic over that VPN connection to solve an DNS problem connecting to the SQL Server) and have following response:

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

    Also fired successfully some Statements and get the expected … fantastic!!!!

    Thanks a lot Jared.
    I really appreciate your help (!).

  12. FWIW, I’m running 10.7.2

Post a comment

Copyright © Jared Folkins
Programming, Computers, Writing, Economics, and Life

Powered by WordPress