What's causing 'unable to connect to data source' for pyodbc?

I try with:

  • MS SQL 2008 Datacenter
  • Ubuntu 12.04 TLS (amd64)
  • Python 2.7

And this works for me:

Test connection:

tsql -H 10.19.4.42 -p 1433 -U DAVIDG -P 123456

on /etc/odbcinst.ini add:

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup =  /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1

on /etc/odbc.ini add:

[SQLDemo]
Description=my dsn
Driver=FreeTDS
Database=teste3
Servername=SQLDemo

on /etc/freetds/freetds.conf add:

[SQLDemo]
        host = 10.19.4.42
        port = 1433
        tds version = 8.0

test with test.py:

#!/usr/bin/python

import pyodbc
cnx = pyodbc.connect("DSN=SQLDemo;UID=DAVIDG;PWD=123456")

cursor = cnx.cursor()
cursor.execute("select * from Company;")
for row in cursor:
  print row.Name

I had the same problem and I found out that it was missing the TDS_Version parameter in the call to connect(). The following code works for me to connect to an instance of MS SQL Server 2008:

import pyodbc

driver = '/opt/local/lib/libtdsodbc.so' # Change this to where FreeTDS installed the driver libaray!

conn = pyodbc.connect(
    driver = driver,
    TDS_Version = '7.2', # Use for
    server = '<hostname or ip address>',
    port = 1433,
    database = '<database>',
    uid = '<uid>',
    pwd = '<pwd>')

After hours of going in circles it turns out all I was missing was

TDS_Version = 8.0 in the DSN in my odbc.ini file.

I had specified it elsewhere, but it needed to be here, too, apparently.

Hope this helps some other poor soul.