Connect to SQL Server instance using pymssql

python sql-server pymssql
Allan picture Allan · Feb 6, 2012 · Viewed 10.1k times · Source

I'm attempting to connect to a SQL Server instance from a Windows box using pymssql (version 2.0.0b1-dev-20111019 with Python 2.7.1). I've tried the most basic approach from the console:

import pymssql
c = pymssql.connect(host = r'servername\instance',
                    user = 'username',
                    password = 'userpassword')

In response to this, I get the very helpful error: InterfaceError: Connection to the database failed for an unknown reason.

I am reasonably confident that the connection information is correct, as it works when I use adodbapi, with the following commands:

import adodbapi
c = adodbapi.connect(r'Provider=sqloledb;Data Source=servername\instance;User ID=username;password=userpassword;'
c.close

I've tried adding the port number to the host parameter, with the same result. Does anyone have a suggestion on how to go about resolving this issue?

Incidentally, I've read the responses at "Unable to connect to SQL Server via pymssql". The OP eventually resolved his issue by correctly configuring FreeTDS, which, from what I can tell, is not used by pymssql on Windows.

Based on @cha0site's recommendation, I have tried using just the hostname, rather than the hostname and instance. This resulted in the same error, but it seemed to take longer to generate the error (though the traceback still indicates the same line). The reason I have been specifying the instance is that I was not able to connect using SSMS unless I specified the instance, so I assumed that it would be necessary for other connections.

I've now also tried pymssql.connect(host='servername', user='username', password='userpassword', database='instance') with the same result (based on @Sid's comment). Based on the pymssql documentation, I believe the database parameter is used to specify the initial database that the user is to be connected to, rather than the instance.

Just to clarify, "instance" is the name provided during installation of SQL Server, not a database within that installation. It occurs to me that it's possible that pymssql does not support this notation, so I will look into re-configuring the SQL Server instance so that it is not required.

I've now re-installed SQL Server as a default instance, rather than a named instance, which allows me to connect without specifying the instance name. adodbapi still works (without /instance), but pymssql still returns the same error. I've also removed and re-installed pymssql from a freshly downloaded archive (still the same version).

Answer

Voislav Sauca picture Voislav Sauca · Apr 12, 2012

Check your freetds.conf file and see if you have set the port 1219., then check again the connexion:

DB = pymssql.connect(host='DB',user='youruser',password='yourpwd',database='yourDBname')

Edit: example of my freetds.conf file Python:

host = 'IP'
port = 1219

Related questions

MSSQL in python 2.7

Is there a module available for connection of MSSQL and python 2.7? I downloaded pymssql but it is for python 2.6. Is there any equivalent module for python 2.7? I am not aware of it if anyone can provide links. Important note: in …

Read More
python and pymssql

I'm new to python. I'm trying to query a MSSQL database. import pymssql conn = pymssql.connect(host='hostname', user='username', password='password', database='dbname') cursor = conn.cursor() sql = "select count(*) from T_Email with (nolock) where Transmit is null" cursor.…

Read More
Using pymssql to insert datetime object into SQL Server

How do I insert a datatime object using pymssql? I know that the SQL Server table is expecting a datetime object, let's say in position 3. I've tried all three of these: cursor.execute("INSERT INTO MyTable VALUES(1, 'Having Trouble', datetime.…

Read More