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