Remote connection to MS SQL - Error using pyodbc vs success using SQL Server Management Studio

Question

I have a MS SQL database in the same network but in other computer. Using the SQL Server Management Studio (SSMS) Express, I can find the database and connect without problems.

But when I use pyodbc to connect to the same server using:

import pyodbc

server = r"xxxER\xxxSQLSERV"
db = "xxxDB"
user = "xxx"
password = "xxxx"
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server + ';DATABASE=' + db +';UID=' + user + ';PWD=' + password)

I get following error:

pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC SQL Server Driver]Login timeout expired (0) (SQLDriverConnect)')

OBS: I guess that the server string should be right, since if I change it I get always the following error:

pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)')

Here the image showing success while using SQL Server Studio Express to connect remotely.

Solution 1

Try specifying the port:

import pyodbc

server = r"xxxER\xxxSQLSERV"
db = "xxxDB"
user = "xxx"
password = "xxxx"
port = "1433"
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';PORT=' + port + ';DATABASE=' + db +';UID=' + user + ';PWD=' + password)

If you're still having issues, try using the IP or FQDN of the server.

Solution 2

"But why ...?"

For those interested in why SQL Server Management Studio (SSMS) can connect to servername\instance while other applications (like our pyodbc apps) cannot, it's because SSMS keeps an MRU (Most Recently Used) list of port numbers in the Windows registry at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect

Each MRU entry (registry value) looks something like this:

Name: PANORAMA\SQLEXPRESS
Type: REG_SZ 
Data: -1006030326:tcp:PANORAMA,52865

Once SSMS has successfully connected by instance name via the SQL Browser service on the remote machine, it can continue to connect by instance name even if the SQL Browser is no longer running on the remote machine, provided that the port number has not changed. Apps that don't use this MRU list (like our pyodbc app) need to have the SQL Browser service running on the remote machine every time they want to connect by instance name.

The most common scenario:

• I want to connect to YOUR-PC\SQLEXPRESS. I try doing that from SSMS on MY-PC, but it doesn't work because the SQL Browser was installed with "Start Mode" set to "Manual" on YOUR-PC.
• I ask you to start the SQL Browser service on YOUR-PC, and you kindly comply, but you just start the service and forget to change the "Start Mode" setting to "Automatic".
• I am able to connect via SSMS (which caches the YOUR-PC\SQLEXPRESS port in the MRU). My python app can connect, too.
• After the next time YOUR-PC restarts, I can connect via SSMS (via the MRU) but my python app cannot (because the SQL Browser service is no longer running on YOUR-PC).

Solution 3

Try changing the Driver from 'SQL Server' to 'SQL Server Native Client 11.0'.

I had the same error message and this fixed it for me.

Solution 4

I have this problem.I can connect with Management Studio (SSMS) but not work with pyodbc. I add version odbc of sql and worked.

change your code to:

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server + ';DATABASE=' + db +';UID=' + user + ';PWD=' + password)

If not work change version 17 to 13 if not to 11 . List versions of ODBC.

View more solutions

Comments

[Gord Thompson]

Just a note: Microsoft's ODBC drivers for SQL Server do not support a PORT= attribute in the connection string. To specify the port it must be appended to the server name/IP after a comma, e.g., 192.168.1.128,1433.

[Gord Thompson]

I suppose it's possible that with some configurations of modern SQL Server versions the ancient "SQL Server" driver (designed for SQL Server 2000 and earlier) may no longer be able to connect.