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
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
Hi Lars,
What is the output from these commands?
odbcinst -j
odbcinst -q -d
odbcinst -q -s
$ 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]
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
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.
$ 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
So is the problem solved?
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
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.
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.
Sounds good.
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
(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 (!).
Well done!
FWIW, I’m running 10.7.2