[Python] Using msal token to authenticate towards Azure SQL database

I have a python based Docker container to be run on Azure Kubernetes and need to connect to an Azure SQL database. I previously used the adal package for Python, but due to security reasons, we want to update to msal and User Identity instead of the old service principle and adal base approach. As this is a run alone app, we do not want to ask or hardcode any passwords.

I can get a token with scope on /.default for databases, but whenever I try to connect to the database via pyodbc.connect, I get a Login failed for user '<token-identified principal>' fault.

This is the code that I have been using with the working setup on the ´adal´ in comments:

import pyodbc
import os
import struct
# import adal
import msal
from azure.keyvault.secrets import SecretClient
from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()
client = SecretClient(vault_url=os.environ.get('AZURE_KEY_VAULT_URI'), credential=credential)

driver = client.get_secret('python-db-driver').value
server = client.get_secret('SqlServerName').value
database = client.get_secret('SqlDbName').value
service_principal_id = client.get_secret('MicroserviceSPId').value
service_principal_password = client.get_secret('MicroserviceSPKey').value

connection_string = f'Driver={driver};SERVER={server};DATABASE={database}'

authority_url = ("https://login.windows.net/" + os.environ['AZURE_TENANT_ID'])
resourceAppIdURI = "https://database.windows.net/"

# Working code using adal
# context = adal.AuthenticationContext(
# authority_url, api_version=None
# )

context = msal.ConfidentialClientApplication(client_id=os.environ['AZURE_CLIENT_ID'],
client_credential=os.environ['AZURE_CLIENT_SECRET'], authority=authority_url)

# Working code using adal
# token = context.acquire_token_with_client_credentials(resourceAppIdURI,
# service_principal_id,
# service_principal_password)

token = context.acquire_token_for_client([resourceAppIdURI + "/.default"])

# get bytes from token obtained
tokenb = bytes(token["access_token"], "UTF-8")

# Working code using adal
# tokenb = bytes(token["accessToken"], "UTF-8")

exptoken = b''
for i in tokenb:
exptoken += bytes({i})
exptoken += bytes(1)
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken

sql_conn = pyodbc.connect(connection_string, attrs_before={1256: tokenstruct})


I get a token without any errors but when setting up the connection to the database, I get the error:

I expected for the token to be usable and working exactly the same as when using ´adal´ and me getting an active connection allowing to interact with the database.

Continue reading...