Today, I worked on a service request that our customer got the following error message using Python code: pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)'). I would like to share with you what was my lessons learned here.
The first thing is to isolate the problem running the suggested Python code using pymssql and pyodbc to see the results:
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName");
conn2 = pymssql.connect(server='servername.database.windows.net', user='username', password='password', database='dbname',port=1433);
cursor = conn.cursor()
cursor.execute('select 1 as a, 2 as b')
row = cursor.fetchone()
print(f"row={row}")
cursor = conn.cursor();
cursor.execute("SELECT 1");
row = cursor.fetchall()
conn.close()
for i in row:
print(i)
cursor2 = conn2.cursor();
cursor2.execute("SELECT 1");
row2 = cursor2.fetchall()
conn2.close()
for i in row2:
print(i)
After this, we saw that a better error message description using pymsql:
File "<stdin>", line 1, in <module>
File "src/pymssql/_pymssql.pyx", line 653, in pymssql._pymssql.connect
pymssql._pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (nodename.trname.northeurope1-a.worker.database.windows.net,11021)\nNet-Lib error during Connection refused (111)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (nodename.trname.northeurope1-a.worker.database.windows.net,11021)\nNet-Lib error during Connection refused (111)\n')
All points to that our customer has as a connection policy Redirect and trying to connect to the port 11021 and the new node (nodename.trname.northeurope1-a.worker.database.windows.net,11021) where the database is running, the connection is refused by any firewall/NSG. Even, as this application worked previously, we need to know that the redirect port and node might be changed.
In this case we have two options:
- Review the firewall policy and outbound range from 11000-12000.
- Change the connection policy to proxy using the port 1433 avoiding the redirection.
Our customer chosen to change the connection policy and they were able to connect. Finally, they are going to review the firewall policy and change the Redirect connection.
For reference: Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Micr...
Enjoy!
