FreeTDS and pyodbc on Mac OS X 10.8 via Homebrew

README.md

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

Following your instructions above I get the following error:

$ python -i test.py
Username: IRMA_RO
Password:
Traceback (most recent call last):
  File "test.py", line 15, in <module>
    c = p.connect("DSN=nadev;UID={0:s};PWD={1:s}".format(username, password))
pyodbc.Error: ('IM002', '[IM002] [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) (SQLDriverConnect)')
>>>

The connection itself seems to work however as both tsql tests work just fine.

Whilst this may not solve the problem in this question with pyodbc or pypyodbc (pure Python) I have found another solution that seems ot have worked out-of-the-box for me from http://stackoverflow.com/questions/11678696/sql-server-python-and-os-x using pymssql and the tutorial: http://pymssql.sourceforge.net/examples_pymssql.php

same problem as @prologic had on the mac. tsql works but not pyodbc. pymssql works well.

I also 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+https://github.com/pymssql/pymssql.git

After that, the simple example here should work great assuming your connection string works :) http://pymssql.org/en/stable/pymssql_examples.html

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"

how can i know in witch port my FREETDS is working ? because i get the go connection on terminal but y can make a connection from laravel 5.8