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 |