Azure Active Directory Managed Identity Authentication support

[robmitchellzone]

Environment

• Python: 3.11.3, 64-bit
• pyodbc: 4.0.34
• OS: Windows 10
• DB: Azure Synapse SQL
• driver: ODBC Driver 17 for SQL Server

Issue

I want to be able to connect to my database running on Azure Synapse (a serverless SQL endpoint) using an Azure Managed Identity object ID. When I try to connect this way, I get an error message saying "Failed to authenticate the user..."

import os
import pyodbc
server = '<my-server>.database.windows.net'
database = '<my-database>'
object_id = '<my-object-id>'
driver= '{ODBC Driver 17 for SQL Server}'

conn = pyodbc.connect(f'driver={driver};server={server};database={database};UID={object_id};Authentication=ActiveDirectoryMsi;Encrypt=Yes;')

The error message I receive is as follows.

Error: ('CE267', "[CE267] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error 12029 opening URL http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F&object_id=<my-object-id> (0) (SQLDriverConnect); [CE267] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error requesting access token, HTTP status -1, expected 200 (0); [CE267] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user '<my-object-id>' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\r\nError code 0xA190; state 41360\r\n (0); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout error [258]. (258); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in login response (258)")

[v-chojas]

Are you running this in an Azure VM which has been assigned the MSI?

[robmitchellzone]

No, I am running it on my local machine. I assigned the identity to the Synapse Workspace. Forgive me if this is user error, I'm not very familiar with Azure's security features. For context: connecting with a username and password doesn't work for me due to my org's 2FA policy. Interactive login works but I don't want to have to sign in every time I connect. SQL Authentication works but doesn't allow me to access external tables, which I need to be able to do.

[v-chojas]

That explains it. Managed Identity is a feature of Azure services. In Azure VMs there is a "token dispenser" that listens on the IP visible in your error message, which obviously won't be there on your local machine. See the following documentation for more information about this feature.

https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview

https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-arm

https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-linux-vm-access-arm

[robmitchellzone]

I see. Thank you for the explanation. So if I want to keep working on my own machine, it sounds like I will have to use interactive login? But, if I switch to an Azure VM, signing in with the Managed Identity Authentication should work?

[v-chojas]

You can obtain an access token via interactive login using libraries like MSAL (https://github.com/AzureAD/microsoft-authentication-library-for-python) and pass it to the driver (https://github.com/mkleehammer/pyodbc/issues/228). That way you will only need to reauthenticate when the token expires.