SQL Server]Login failed for user '<token-identified principal>'. The server is not currently configured to accept this token. #45216
Description
- Package Name: azure-identity
- Package Version: 1.25.1
- Operating System: ubuntu
- Python Version: 3.12
Describe the bug
SQL Server]Login failed for user ''. The server is not currently configured to accept this token.
To Reproduce
The following simple code base is failing to connect to azure sql server after acquiring a token.
Token acquired via azd auth login --scope https://database.windows.net/.default
Execute this code.
from dotenv import load_dotenv
import struct
import pyodbc
from azure.identity import DefaultAzureCredential
import os
import logging
load_dotenv()
logging.basicConfig(level=logging.DEBUG)
def get_access_token(scope: str = "https://database.windows.net/.default") -> str:
cred = DefaultAzureCredential(logging_enable=True)
token = cred.get_token(scope)
return token.token
def connect_with_managed_identity(server: str, database: str, driver: str = "ODBC Driver 18 for SQL Server") -> pyodbc.Connection:
access_token = get_access_token()
token_bytes = access_token.encode("utf-8")
token_struct = struct.pack("=i", len(token_bytes)) + token_bytes
conn_str = (
f"Driver={{{driver}}};"
f"Server={server};"
f"Database={database};"
f"Encrypt=yes;"
f"TrustServerCertificate=yes;"
# f"Authentication=ActiveDirectoryAccessToken;"
)
conn = pyodbc.connect(conn_str, attrs_before={1256: token_struct})
return conn
def main():
# Replace with your server and database
SERVER = os.environ.get("AZURE_SQL_SERVER", "sql-sw-smoke-aue-dev.database.windows.net,1433")
DATABASE = os.environ.get("AZURE_SQL_DATABASE", "sdb-sw-smoke-aue-dev")
print(SERVER,DATABASE)
print('TenantId')
print(os.environ.get("AZURE_TENANT_ID"))
try:
with connect_with_managed_identity(f"{SERVER}.database.windows.net", DATABASE) as conn:
cur = conn.cursor()
cur.execute("SELECT TOP 1 name FROM sys.databases;")
row = cur.fetchone()
print("Connected. Sample row:", row)
except Exception as e:
print("Connection failed:", e)
if __name__ == "__main__":
main()
Expected behavior
The expectation is that the sql statement is executed.
Additional context
The database is known to be configured correctly because a nodejs client is successful connecting and executing sql queries when the AzureDefaultCredential is configured as:
const credential = new DefaultAzureCredential({
tenantId: config.directory,
})
If the nodejs code is
const credential = new DefaultAzureCredential()
the same issue appears as in python.
With the logging enabled, I see the azure-identity try different token acquistion methods and then ultimately get the token from azd.
azure.identity._exceptions.CredentialUnavailableError: PowerShell is not installed
DEBUG:azure.identity._credentials.azd_cli:Executing subprocess with the following arguments ['/usr/local/bin/azd', 'auth', 'token', '--output', 'json', '--no-prompt', '--scope', 'https://database.windows.net/.default']
DEBUG:azure.identity._internal.decorators:AzureDeveloperCliCredential.get_token succeeded
DEBUG:azure.identity._internal.decorators:[Authenticated account] Client ID: 04b07795-8ddb-461a-bbee-02f9e1bf7b46. Tenant ID: c1bd07b1-b7b6-47ca-9ef0-cf66735daae6. User Principal Name: Phil.Tomlinson@aurecongroup.com. Object ID (user): 029bba5c-cc0f-4ddd-bc39-e719f75ea2c3
INFO:azure.identity._credentials.chained:DefaultAzureCredential acquired a token from AzureDeveloperCliCredential
Database auditing is enabled in the azure sql server instance and the following appears in the database log file.
select * from sys.fn_get_audit_file('https://stacazureidentity.blob.core.windows.net/sqldbauditlogs/sqlsidentity/master/SqlDbAuditing_ServerAudit_NoRetention/2026-02-10/08_09_20_644_0.xel'
,DEFAULT
,DEFAULT
);
There are records in the db which have
<login_information>
<error_code>
18456
</error_code>
<error_state>
132
</error_state>
</login_information>
So connectivity to the db is not an issue
The JWT token is this.
{
"aud": "https://management.azure.com/",
"iss": "https://sts.windows.net/c1bd07b1-b7b6-47ca-9ef0-cf66735daae6/",
"iat": 1771278916,
"nbf": 1771278916,
"exp": 1771283174,
"acr": "1",
"acrs": [
"p1"
],
"aio": "AXQAi/8bAAAAnSJD6lawkz0JAUUKrO4Emhtm9EJDAcwjHHstPuei3slXIseMtx+NKfIkED/ielagie0aLhycQzMkomyaf47WLz8qMnYOkRZXECik7GTdKVeLigizB+vh+CoWPyb30uMiFw2HZLfsOCHE0jdu1REnlA==",
"amr": [
"pwd",
"mfa"
],
"appid": "04b07795-8ddb-461a-bbee-02f9e1bf7b46",
"appidacr": "0",
"family_name": "redacted",
"given_name": "redacted",
"groups": [ ],
"idtyp": "user",
"ipaddr": "117.20.69.111",
"name": "redacted",
"oid": "029bba5c-cc0f-4ddd-bc39-e719f75ea2c3",
"onprem_sid": "S-1-5-21-1975116412-1890355937-599114201-291335",
"puid": "100320004DD81DC8",
"rh": "1.AWYAsQe9wba3ykee8M9mc12q5kZIf3kAutdPukPawfj2MBNmAIlmAA.",
"scp": "user_impersonation",
"sid": "001f4fba-ab7c-c77a-d659-6f914ebff1a3",
"sub": "Q48gWYrAXihUyQ6m7val4fqZnq24tZxU1-KAdp0k2WA",
"tid": "c1bd07b1-b7b6-47ca-9ef0-cf66735daae6",
"unique_name": "redacted@aurecongroup.com",
"upn": "redacted@aurecongroup.com",
"uti": "q_aaDskpbUKj8ORIRa41AA",
"ver": "1.0",
"wids": [
"b79fbf4d-3ef9-4689-8143-76b194e85509"
],
"xms_act_fct": "5 3",
"xms_ftd": "QrEI7jV9Ls5sIo8vxs1619fZ5V3o7mfOKmTxinLJcUABYXVzdHJhbGlhc291dGhlYXN0LWRzbXM",
"xms_idrel": "1 28",
"xms_sub_fct": "2 3",
"xms_tcdt": 1402357282
}
Is the AUD appropriate?
I question the tid value. We have a multi-tenant set up.
Users are in the c1bd07b1-.... directory
Resources (SQL Server) are in the 951b1ed2-..... directory
EXP should not be an issue because the node js client can successfully authenticated and execute queries.
As far as I know there is not way to see what azure sql server thinks is incorrect about the token.