First, install the following libraries:
$ brew install unixodbc
$ brew install freetds --with-unixodbc
FreeTDS should already work now, without configuration:
$ tsql -S [IP or hostname] -U [username] -P [password]
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> ^D
Onto unixODBC, we need to link to the driver, edit /usr/local/etc/odbcinst.ini:
[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1
The test command we're using requires configuring a DSN, so edit /usr/local/etc/odbc.ini:
[MYDSN]
Driver = FreeTDS
Server = [IP address]
Port = 1433
The configuration for your DNS might vary, you might need the TDS_Version or Servername directives. The above worked for me for SQL Server 2008 R2. Now, run the test command:
$ isql MYDSN [username] [password] -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> ^D
If the test succeeded, you can continue onto installing the Python library pyodbc. Version 3.0.7 and before didn't link with unixODBC, so a change had to be made to setup.py, follow the steps at the bottom instead. The current version (3.0.10) has resolve this issue, so you can go ahead and install from pypi:
pip install pyodbc
Now pyodbc should work:
import pyodbc
pyodbc.connect('DSN=MYDSN;UID=[username];PWD=[password]')
You don't need to have your DSN configured in odbc.ini, so clear that file. You probably want to select a database on connect, so change your connect line to read:
pyodbc.connect('DRIVER=FreeTDS;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')
Note that you could also link to the library file of FreeTDS instead of using odbcinst.ini, like this:
pyodbc.connect('DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')
Version 3.0.7 didn't link with unixODBC on OS X, so a change had to be made to setup.py. Note that the current version of writing (3.0.10) doesn't have this issue. Follow these steps only to install the old version. Download the source package and extract it somewhere. Find the following lines (146-147):
elif sys.platform == 'darwin':
# OS/X now ships with iODBC.
And change this line:
settings['libraries'].append('iodbc')
into:
settings['libraries'].append('odbc')
Then run the following command to install:
> python install .
There are many instructions on the internet about how to do this, but yours were both the simplest and the only ones that actually worked for me.
@raagha-b You can just leave off the --with-unixodbc option - it's installed by default now.
The only adjustment I had to make, which I'm mentioning here in case it helps anyone else, is that my username is an active directory login with format MY_COMPANY\USERNAME, and the above instructions only worked with quotes around the whole thing ("MY_COMPANY\USERNAME").