After spending many hours trying to get FreeTDS and unixodbc to run on a Mac OS X 10.8 system with the python module, pyodbc, I eventually came to this recipe, which is remarkably simple thanks to homebrew. I also found unixodbc was unnecessary and I couldn't get it to play well with FreeTDS, so this install does not include unixodbc. See also http://www.acloudtree.com/how-to-install-freetds-and-unixodbc-on-osx-using-homebrew-for-use-with-ruby-php-and-perl/ and http://www.cerebralmastication.com/2013/01/installing-debugging-odbc-on-mac-os-x/.
Prerequisites: Be sure you have XCode and the Commandline Tools for XCode installed from Apple. Also install homebrew followed with brew update and brew doctor.
Install FreeTDS:
brew install freetds
Test your install:
tsql -H <yourdbhost> -p <yourdbport> -U <yourusername> -P <yourpassword>
You should get something like:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
That means you have made a connection! Type exit.
IMPORTANT If you do not supply a username and password, you will get an error and not create a connection. This principle applies to the python script you supply, too!
You have in your BREW_INSTALL_DIR/freetds/0.91/etc/ folder a freetds.conf file. Edit it with your server info.
Test your DSN configuration from the freetds.conf file:
tsql -S <yourdsn> -U <yourusername> -P <yourpassword>
You should get the same response:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> exit
If you get an error, including Error 100 (severity 11): unrecognized msgno, try setting your tds version to different numbers. I found 7.1 was the setting I needed for connecting to a MS SQL 2008 R2 server, even though http://freetds.schemamania.org/userguide/choosingtdsprotocol.htm describes 7.2!
Once the DSN configuration is going, install pyodbc.
pip install pyodbc
You should be able to connect and test from python (as per https://code.google.com/p/pyodbc/wiki/GettingStarted):
import pyodbc
import getpass
uid=raw_input("Username:")
pwd=getpass.getpass()
cnxn = pyodbc.connect("DSN=<yourdsnfrom_freetds.conf>;UID={0};PWD={1}".format(uid, pwd))
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
if row:
print row
Great guide, thanks! Just wanted to add, for those looking to connect to an Azure MS SQL server I've found that using a username in the pyodbc (and tsql/isql calls) with username@host.domain to be quintessential for whether or not I would succeed in the connections.
I.e. With Azure I would have a user in the connection string being "myuser@myserver.database.windows.net"