How to configure a Python application to use a passwordless connection with a Azure SQL Database? - Stack Overflow

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,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("/.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: )

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

Any advise would help us.

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.

• python
• azure-sql-database
• pyodbc
• microsoft-entra-id
• azure-identity

Share Improve this question Follow edited Jan 31 at 16:16 Thom A 96.3k1111 gold badges6161 silver badges9595 bronze badges asked Jan 31 at 16:11 Rakesh VRakesh V 411111 bronze badges 8

• add the MSI as a contained database user in your Azure SQL Database. – Dileep Raj Narayan Thumula Commented Feb 4 at 10:32
• well, I have created new MSI and assigned from SQL DB. Also added MSI as user to the SQL DB. Still I'm getting "Login failed for user '<token-identified principal>'". – Rakesh V Commented Feb 4 at 12:04
• import os import pyodbc import requests import struct dilip_identity_endpoint = os.environ["IDENTITY_ENDPOINT"] dilip_identity_header = os.environ["IDENTITY_HEADER"] dilip_resource_uri = "database.windows.net" dilip_token_auth_uri = f"{dilip_identity_endpoint}?resource={dilip_resource_uri}&api-version=2019-08-01" dilip_head_msi = {'X-IDENTITY-HEADER': dilip_identity_header} dilip_resp = requests.get(dilip_token_auth_uri, headers=dilip_head_msi) – Dileep Raj Narayan Thumula Commented Feb 4 at 12:34
• dilip_access_token = dilip_resp.json()['access_token'] dilip_accessToken = bytes(dilip_access_token, 'utf-8') dilip_exptoken = b"" for dilip_i in dilip_accessToken: dilip_exptoken += bytes({dilip_i}) dilip_exptoken += bytes(1) dilip_tokenstruct = struct.pack("=i", len(dilip_exptoken)) + dilip_exptoken dilip_conn = pyodbc.connect( "Driver={ODBC Driver 17 for SQL Server};Server=tcp:dilipserver.database.windows.net,1433;Database=dilipdatabase", attrs_before={1256: bytearray(dilip_tokenstruct)} ) – Dileep Raj Narayan Thumula Commented Feb 4 at 12:35
• dilip_cursor = dilip_conn.cursor() dilip_cursor.execute("SELECT @@VERSION") dilip_rows = dilip_cursor.fetchall() for dilip_row in dilip_rows: print(dilip_row) – Dileep Raj Narayan Thumula Commented Feb 4 at 12:35

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

From my case, seems the issue is with the token. So I have upgraded azure-identity and pyodbc versions, and then it works as expected.