[Python] How to configure a Python application to use a passwordless connection with a Azure...

We are trying to configure passwordless connections with Azure SQL Database using Python application. So we followed Microsoft Spec document Migrate a Python application to use passwordless connections with Azure SQL Database and implemented as

• Set admin as Entra ID User
• then assigned roles

CREATE USER [user@domain] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user@domain];
ALTER ROLE db_datawriter ADD MEMBER [user@domain];
ALTER ROLE db_ddladmin ADD MEMBER [user@domain];
GO


Updated the local connection configuration

import os
import pyodbc, struct
from azure.identity import DefaultAzureCredential

connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30"

def get_all():
with get_conn() as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM Persons")
# Do something with the data
return

def get_conn():
credential = DefaultAzureCredential(exclude_interactive_browser_credential=False)
token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
SQL_COPT_SS_ACCESS_TOKEN = 1256 # This connection option is defined by microsoft in msodbcsql.h
conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
return conn


Then trying to access any table from the database, but getting an error

InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '<token-identified principal>'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '<token-identified principal>'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)")
(Background on this error at: https://sqlalche.me/e/20/rvf5)


If it is works locally, then will implement further for Azure.

Any advise would help us.

Continue reading...