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

Question

I'm trying to connect to an MSSQL database from python on Linux (SLES).

I have installed pyodbc and Free TDS. From the command line:

tsql -H server -p 1433 -U username -P password 

Connects to the server without a problem, however, from Python:

import pyodbc pyodbc.connect(driver='{FreeTDS}', server='server', database='database', uid='username', pwd='password') 

Yields an error:

pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)') 

I'm finding this error unhelpfully vague. Even a suggestion to narrow down the issue would be helpful right now.

Edit: Looking at the TDS log dump it looks like this is where the whole thing falls apart:

token.c:328:tds_process_login_tokens() util.c:331:tdserror(0x87bbeb8, 0x8861820, 20017, 115) odbc.c:2270:msgno 20017 20003 util.c:361:tdserror: client library returned TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2) util.c:156:Changed query state from IDLE to DEAD token.c:337:looking for login token, got  0() token.c:122:tds_process_default_tokens() marker is 0() token.c:125:leaving tds_process_default_tokens() connection dead login.c:466:login packet accepted util.c:331:tdserror(0x87bbeb8, 0x8861820, 20002, 0) odbc.c:2270:msgno 20002 20003 util.c:361:tdserror: client library returned TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2) mem.c:615:tds_free_all_results() error.c:412:odbc_errs_add: "Unable to connect to data source" 

Answer 1

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 

Answer 2

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