I am trying to connect to an instance of SQL Server from a windows window using pymssql (version 2.0.0b1-dev-20111019 with Python 2.7.1). I tried the simplest approach from the console:
import pymssql c = pymssql.connect(host = r'servername\instance', user = 'username', password = 'userpassword')
In response to this, I get a very useful error: InterfaceError: Connection to the database failed for an unknown reason.
I am sure 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 tried adding a port number to the host parameter with the same result. Does anyone have a suggestion on how to solve this problem?
By the way, I read the answers on Failed to connect to SQL Server via pymssql . "OP finally resolved my problem by correctly setting up FreeTDS, which, as I can tell, is not used by pymssql for Windows.
Based on the @ cha0site recommendation, I tried to use only the host name, not the host name and instance. This led to the same error, but it took longer to generate the error (although the trace still indicates the same line). The reason I specified the instance is because I could not connect using SSMS if I did not specify the instance, so I suggested that this is necessary for other connections.
I also tried pymssql.connect(host='servername', user='username', password='userpassword', database='instance') with the same result (based on @Sid comment). Based on the pymssql documentation , I believe that the database parameter is used to specify the source database to which the user should be connected, rather than the instance.
To clarify, an “instance” is the name that was specified during the installation of SQL Server, and not the database as part of this installation. It seems to me that pymssql does not support this notation, so I will consider reconfiguring an instance of SQL Server so that it is not required.
Now, I re-installed SQL Server as the default instance, not 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 also uninstalled and reinstalled pymssql from a recently downloaded archive (all the same option).