Connecting to MS SQL Server with iODBC on Mac

Question

I'm trying to connect to a Microsoft SQL Server from my Mac development machine using iODBC. No matter what I do, I get the error message Unable to connect to data source from the FreeTDS driver. After consulting various manuals, blog posts, and StackOverflow questions, I'm at my wit's end.

I'm using Mac OS X 10.7.4, with FreeTDS version 0.91 installed through Homebrew. Here's what I'm doing:

$ brew install freetds
$ mkvirtualenv odbc
$ workon odbc
$ pip install pyodbc

$ tsql -H localhost -U mydbusername -P mydbpassword -p 1433 
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> quit

The above connects and works, but:

$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list): driver={TDS};server=localhost;uid=mydbusername;pwd=mydbpassword;database=mydbname
1: SQLDriverConnect = [FreeTDS][SQL Server]Unable to connect to data source (0) SQLSTATE=08001
1: ODBC_Connect = [FreeTDS][SQL Server]Unable to connect to data source (0) SQLSTATE=08001

Since I'm ultimately planning to use it through PyODBC, I tried that as well:

$ python
Python 2.7.3 (default, Jun 22 2012, 00:35:38) 
[GCC 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.15.00)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> c = pyodbc.connect(driver='/usr/local/lib/libtdsodbc.so', host='localhost', name='mydbname', uid='mydbusername', pwd='mydbpassword')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I've tried all manner of things suggested in blog posts and SO posts... creating DSNs in ODBC Administrator, creating a ~/.odbc.ini, attempting to use unixODBC (which didn't work with PyODBC), specifying a connection string instead of keyword args, etc. I also produced a trace file through iODBC, which I have pasted here, but I couldn't glean any more information from it than from the error message.

(I should mention that I am tunneling the connection to SQL Server over SSH with the line LocalForward localhost:1433 production.someinstitution.edu:1433 in my ~/.ssh/config, since my IT department has limited me to connect only from the production server's IP. It works for a GUI client (SQuirreLSQL) so I assume the forwarding is correct.)

Answer 1

You should use 'port' parameter in connection string in case of DSN-less connection.

Otherwise your port could be specified in your DSN or driver config (odbc.ini and freetds.conf respectively)