Thursday, January 13, 2022

Lesson Learned #183: TDSDump in Python connecting to Azure SQL Managed Instance

Today, I worked on a service request that our customer received an error message "pymssql.OperationalError: (40532, 'Cannot open server "1433" requested by the login" connecting from Python version 2.7.17 in Ubuntu v.18.04 to Azure SQL Managed Instance. 

 

During my troubleshooting process I found that using pyodbc with ODBC Driver 17 I was able to connect but using pymssql, I got the error message. 

 

However, using the tool TSQL I was able to connect, so, all points to that something is happening. Finally, after multiple tests using TDSDump and based on the strange error message that I got, I found a strange behaviour how pymssql or Python 2.7.17 is managing the names like we have in Azure SQL Managed Instance, for example,  for private endpoint (servername.virtualcluster.database.windows.net),  for public endpoint (servername.public.virtualcluster.database.windows.net) 

 

In this situation, installing a new version of Python, in this case, the version 3, I was able to connect using pymssql and pyodbc. I found a very useful in this type of issues the usage of TDSDump, because you could find information, like, TDS version, how is the connection goes, etc.. 

 

 

 

import os
os.environ['TDSDUMP'] = 'stdout'
import pymssql
import pyodbc

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servermanagedinstance.virtualcluster.database.windows.net,1433;UID=username;PWD=password;database=dbname");  

conn2 = pymssql.connect(server='servermanagedinstance.virtualcluster.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)

 

 

 

Enjoy!

Posted at https://sl.advdat.com/3GtBhgr