0

I have been using pymssql for a while with no issues. I can connect to on-prem and azure sql db.

However, we moved an on-prem db to azure MANAGED Instance and the same scripts don't work after altering the connect server variables.

conn = pymssql.connect(host='mysrv-sql.da0b68f6c.database.windows.net', user='myuser', password='mypass',  database='mydb')
conn.autocommit(True)
cur = conn.cursor()

I have tried all combinations of the username (with or without @mysrv-sql, @mysrv-sql.da0b..., etc)

I just get the generic: DB-Lib 20002 Error, Adaptive Server connection failed

NOTE: I can connect to the managed instance using TSQL or SSMS on the same machine python is executed.

FYI: Using wheel 2.1.4 with FreeTDS, I have a feeling the problem is related to Managed Instance forcing Encryption

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Robin Halabicki
  • 37
  • 2
  • 8
  • Did you review this? http://pymssql.org/en/stable/azure.html – user8834780 Oct 15 '18 at 17:24
  • yes, using that and my binaries and lib, I can connect to an Azure SQL DB no problem, changing the server (using the same user and password that exists and I can login with via SSMS to both Azure SQL and Azure Managed Instance) I get the 20002 error when attempting to connect via python. – Robin Halabicki Oct 15 '18 at 17:49
  • Basically, Same user/password in both Azure SQL DB and Azure Managed Instance Using said password, I can login to Both Azure SQL and Azure Managed from SSMS on machine running python In python, Using a connection string I can use said username/password and connect to Azure SQL DB Changing python server to Azure managed fails – Robin Halabicki Oct 15 '18 at 17:51
  • See if this helps https://stackoverflow.com/questions/32380367/pymssql-connection-to-the-database-only-works-sometimes/32435770#32435770 – user8834780 Oct 15 '18 at 17:59
  • yes, I've read them. Those threads relate to azure sql db which I have no issue connecting to. – Robin Halabicki Oct 15 '18 at 18:24
  • Even if only for diagnostic purposes, can you try connecting via pyodbc with Microsoft's "ODBC Driver 17 for SQL Server" and see if that works any better? – Gord Thompson Oct 17 '18 at 13:30
  • As an example using pytds lib is `pytds.tds_base.Error: Client does not have encryption enabled but it is required by server, enable encryption and try connecting again` – Robin Halabicki Oct 17 '18 at 15:13

0 Answers0