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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.