Skip to content

Instantly share code, notes, and snippets.

Last active September 22, 2023 17:23
Show Gist options
  • Save Bouke/10454272 to your computer and use it in GitHub Desktop.
Save Bouke/10454272 to your computer and use it in GitHub Desktop.
Install FreeTDS, unixODBC and pyodbc on OS X

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:

Description = TD Driver (MSSQL)
Driver = /usr/local/lib/
Setup = /usr/local/lib/
FileUsage = 1

The test command we're using requires configuring a DSN, so edit /usr/local/etc/odbc.ini:

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                                  |
|                                       |

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, 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

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/;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')

Reference -- Installing pyodbc version 3.0.7 and before

Version 3.0.7 didn't link with unixODBC on OS X, so a change had to be made to 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:




Then run the following command to install:

> python install .
Copy link

Thanks, big help.

Copy link

isofer commented Jul 31, 2014

I waisted soooo much time installing and debugging...
you solved my problems. thank you so much!!

Copy link

This works up until I install pyodbc from the source. Then my homebrewed system Python shell cannot import pyodbc. Any tips?

python install put my pyodbc here:


Copy link

8 hours in I finally found this post. THANK YOU

Copy link

dbkaplun commented Jun 2, 2015

FYI, looks like the newest version of pyodbc (3.0.10) now uses unixodbc instead of iodbc for OSX.

Copy link

thanks for this! I have searched for hours ...

Copy link

Bounder commented Sep 11, 2015

Thank you so much! I had to reinstall this odbc crap after some cleaning up on my and this really helped. You're the best!

Copy link

Bouke commented Sep 11, 2015

@dbkaplun thanks, I have updated the instructions so pyodbc is installed from pypi instead.

Copy link


a brew rm unixodbc and a brew rm freetds helps beforehand

Copy link

this worked! also +1 to the tip by @soobrosa

Copy link

Anybody has any idea on how to get bsqldb or freebcp try not to connect to master in Azure? Seems like the -D now works with TSQL.

Copy link

jonneff commented Mar 28, 2016

You Rock!!! Thanks also to soobrosa for brew rm advice.

Copy link

benflips commented Jun 3, 2016

I too sing your praises. Thanks!

(@soobrosa: also an honourable mention for the brew rm)

Copy link

This is awesome. I had a problem in installing RODBC package and this helped me out. Thanks.

Copy link

thekoc commented Jun 30, 2016

Thanks, helped me a lot

Copy link

arq101 commented Sep 16, 2016

A big thank you. After spending almost a whole day tinkering around with other solutions and not succeeding, these instructions finally worked.

Copy link

What about freetds.conf?

Copy link

I found that pymssql worked a lot better and with less fuss than trying to manually setup FreeTDS, unixODBC, and pyodbc together.

Assuming you're here for OS X/macOS instructions and are using TDS_Version 8.0 (SQL Server 2014), you'll need to install version 2.2.0 or above of pymssql which hasn't made it to PyPI yet (see this issue). Run the following command to install:

pip install git+

After that, the simple example here should work great assuming your connection string works :)

Copy link

agu3rra commented Jan 5, 2017

FreeTDS no longer generates .so objects to be used as drivers on the odbc.ini file. Are there any workarounds? Thanks!

Copy link

The first line brew install unixodbc is unnecessary, since the second line brew install freetds --with-unixodbc will install unixodbc.

Copy link

Thanks for this. Well written and works great for me.

Copy link

a-pagano commented Dec 5, 2017

Works like a charm thanks a lot 👌

Copy link

VMerlin commented Aug 16, 2018

How can i connect to Veritca with the same ? Im getting errors with [] due to:SQLDriverConnect: {01000} [unixODBC][Driver Manager]Can't open lib 'Vertica' : file not found

Copy link

Hey, JD Long here... I was googling for this and found it helpful... then I realized you wrote it based on some notes I made years ago... I can't stop laughing.

Copy link

Latest brew stopped supporting --with option. Anyone have a work around for this ?

Copy link

cwade commented May 15, 2019

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").

Copy link

Thanks for the fantastic explanation. This is the first tutorial I came across that actually worked! Big cheers. :)

Copy link

If somebody comes across this and wants to use mac ports instead, I made my own gist:

Copy link

manamhr commented Nov 1, 2022

I tried this, but now it gives me this error after running:
isql MYDSN [username] [password] -v

[01000][unixODBC][Driver Manager]Can't open lib '/usr/local/lib/' : file not found
[ISQL]ERROR: Could not SQLConnect

'I have Apple M1 Pro. have the same problem with changing the path to /opt/homebrew/Cellar/unixodbc/2.3.11/lib/
same problem with pyodbc
I am losing my mind lol

Copy link

nightvision04 commented Jan 23, 2023

@manamhr I am running into the same problem:

After running the python command pyodbc.connect('DRIVER=FreeTDS;SERVER=localhost;DATABASE=mydb;UID=myuser;PWD=mypassword'), I get the following error. I have linked the driver in the odbc configuration.

'01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/local/lib/' : file not found (0) (SQLDriverConnect)"

I am on a mac m1. I tried using both the macports and brew guides mentioned above.

Here's the output of otool -L /opt/local/lib/

	/opt/local/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
	/opt/local/lib/libodbcinst.2.dylib (compatibility version 3.0.0, current version 3.0.0)
	/opt/local/lib/libiconv.2.dylib (compatibility version 9.0.0, current version 9.1.0)
	/opt/local/lib/libhogweed.6.dylib (compatibility version 6.0.0, current version 6.6.0)
	/opt/local/lib/libgssapi_krb5.2.2.dylib (compatibility version 2.0.0, current version 2.2.0)
	/opt/local/lib/libgnutls.30.dylib (compatibility version 59.0.0, current version 59.2.0)
	/opt/local/lib/libnettle.8.dylib (compatibility version 8.0.0, current version 8.6.0)
	/opt/local/lib/libgmp.10.dylib (compatibility version 15.0.0, current version 15.1.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)

And here is the output of: otool -L /opt/homebrew/lib/

	/opt/homebrew/opt/unixodbc/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
	/opt/homebrew/opt/unixodbc/lib/libodbcinst.2.dylib (compatibility version 3.0.0, current version 3.0.0)
	/usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
	/System/Library/Frameworks/Kerberos.framework/Versions/A/Kerberos (compatibility version 5.0.0, current version 6.0.0)
	/opt/homebrew/opt/openssl@1.1/lib/libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
	/opt/homebrew/opt/openssl@1.1/lib/libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)

The contents of my odbcinst.ini configuration in /etc/odbcinst.ini:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server

Description=ODBC for FreeTDS

To be clear, I am able to successfully connect to my local sql database from within docker and within tsql. So I just need to point pyodbc to use the same drivers that tsql is using.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment