EGr EGr - 2 months ago 20
Python Question

Cannot connect to mssql db using pymssql

I have FreeTDS installed and configured correctly. My freetds.conf file as this appended to the end:

[myserver]
host = myserver
port = 1433
tds version = 7.0


And I can running the following command gives me a SQL prompt:

tsql -S myserver -U username


My python script is extremely minimal, in an attempt to successfully connect to the database:

#! /path/to/python/bins

import pymsql
conn = pymssql.connect(host='myserver', user='username', password='password', database='database', as_dict=True)
conn.close()


But when I run it I recieve the following error:

Traceback (most recent call last):
File "./test.py", line 5, in <module>
conn = pymssql.connect(host='myserver', user='username', password='password', database='database', as_dict=True)
File "pymssql.pyx", line 456, in pymssql.connect (pymssql.c:6017)
pymssql.InterfaceError: Connection to the database failed for an unknown reason.


What could cause this? From what I've searched, most people who run into this problem have the freetds.conf file configured incorrectly; however, I can successfully connect (with tsql). Does anyone know what I'm doing wrong, or how I can fix this?

Answer

I have just looked through pymssql code and most likely you have problem with MSSQL driver. https://code.google.com/p/pymssql/source/browse/pymssql.pyx?name=1.9.908#456

Try configuring logging in FreeTDS to see "unknown reason": see http://freetds.schemamania.org/userguide/logging.htm (mirror)

Basically:

$ export TDSDUMP=/tmp/freetds.log