From Synapse notebook, trouble connecting to Azure SQL using system managed identity

Question

I can create a Linked Service to my Database using my Synapse system-managed identity. This works fine, and the "test connection" shows success.

But for the life of me, I can't figure out how to do it in a Python notebook. Here is the generally recommended method based on my research.

server_name = "someserver.database.windows.net"
database_name = "mydb"
auth_type = "ActiveDirectoryMsi"
conn_str = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server_name};Database={database_name};Authentication={auth_type};"
conn = pyodbc.connect(conn_str)

When I attempt to run this code, I always receive a message indicating it can't authenticate the user :

  Ex:Error: ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\nError code 0xA190; state 41360\n (0) (SQLDriverConnect)")

Why would the linked service creation work but this approach doesn't?

When I check the Synapse settings in the Azure Portal, I do see a managed identity object id listed in the properties (plus, the linked service creation worked). Help?

Answer 1

The problem is how the SQL Server uses or recognizes Active Directory Managed Service Identity (MSI).

Make sure that your authentication method (ActiveDirectoryMsi) is correctly set up in both the Azure Container Instance and the SQL Server configurations.

Also you need the Managed Identity has the necessary permissions on the SQL Server. It should have the correct roles and permissions assigned to access the database.

Here are some threads :

https://stackoverflow.com/questions/46681021/can-not-connect-to-azure-sql-server-using-active-directory-integrated-authentica

https://learn.microsoft.com/en-us/answers/questions/1383555/managed-identity-authentication-from-synapse-noteb