LLI Wiki — My Digital Knowledge Base

Troubleshooting a managed identity that cannot log in to Azure SQL from Azure Batch

Runbook: symptoms, error messages and checks (identity assigned to the pool, CREATE USER FROM EXTERNAL PROVIDER, token audience, firewall, private endpoint, caches).

An Azure Batch task that connects to Azure SQL with a user-assigned managed identity (UAMI) and gets Login failed is a classic, and the cause is rarely where you look first. Here is the runbook I follow, in order, from fastest to heaviest.

Symptoms and messages

Message Lead
Login failed for user '<guid>@<tenant>' (error 18456) token issued, but refused on the SQL side
Login failed for user '<token-identified principal>' same, database user missing or badly created
Client with IP address '...' is not allowed to access the server (40615) server firewall
Login timeout expired / host unreachable network, private endpoint, DNS
Token acquisition fails (Connect-AzAccount -Identity or metadata service call) identity not assigned to the pool

Useful fact: Azure SQL always returns the same state (1) to the client for an 18456, so it says nothing about the cause. The GUID in the message is the identity's ClientID: if it matches the expected identity, the token is fine and the problem is on the SQL side.

Decision flow

flowchart TD
    A["Login failed"] --> B["Token obtained ?"]
    B -->|no| C["Identity assigned to the pool ?"]
    B -->|yes| D["Audience and ClientID correct ?"]
    D -->|no| E["Fix config / audience"]
    D -->|yes| F["User in the database ?"]
    F -->|no| G["CREATE USER FROM EXTERNAL PROVIDER"]
    F -->|yes| H["Network : firewall / private endpoint"]
    H --> I["Auth cache : flush and wait"]

Step 1: is the identity assigned to the pool?

On the node, the metadata service can only issue a token for an identity attached to the pool (or the VM). Check the pool definition lists the UAMI. An identity added to a pool after creation may require recreating or resizing nodes to be visible: test on a recent node.

Step 2: does the configuration point to the right identity?

A frequent mistake: a configuration copied from another environment with only the name changed, so the identifiers (ObjectId, ClientID) belong to a different identity. The token is valid but for the wrong principal, and everything after looks broken. Check in 30 seconds:

Get-AzADServicePrincipal -DisplayName 'uami-dba-batch' |
    Select-Object Id, AppId    # Id = ObjectId, AppId = ClientID

Step 3: decode the token (audience and claims)

From a node where the identity is assigned, request a token for the Azure SQL audience and decode it:

$uri = 'http://169.254.169.254/metadata/identity/oauth2/token' +
       '?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' +
       '&client_id=00000000-0000-0000-0000-000000000000'
$t = (Invoke-RestMethod -Uri $uri -Headers @{ Metadata = 'true' }).access_token
$p = $t.Split('.')[1].Replace('-','+').Replace('_','/')
switch ($p.Length % 4) { 2 { $p += '==' } 3 { $p += '=' } }
[Text.Encoding]::UTF8.GetString([Convert]::FromBase64String($p)) |
    ConvertFrom-Json | Select-Object aud, oid, appid, iat

Check: aud is Azure SQL (resource https://database.windows.net/); oid and appid are the expected ones; iat is recent. Tokens can be cached: a token issued before an Entra change (a group membership, say) does not carry it.

Step 4: does the user exist in the database?

In the target database, as an administrator:

SELECT name, type_desc, authentication_type_desc,
       DATALENGTH(sid) AS sid_bytes
FROM sys.database_principals
WHERE type IN ('E', 'X');       -- E = external user, X = external group

If it is missing:

CREATE USER [uami-dba-batch] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [uami-dba-batch];

The SID must be 16 bytes. Do not validate a SID with CONVERT(uniqueidentifier, sid): the conversion silently truncates and hides a malformed SID. When in doubt, DROP USER and recreate. The identity can also come through an Entra group: then the group must exist as a user and the token must carry the membership.

Step 5: firewall and network

Step 6: caches, then wait

After a fix, flush the database authentication cache and wait:

DBCC FLUSHAUTHCACHE;   -- in the user database, and in master

Propagation can take several minutes (I have seen up to about fifteen). A test that fails seconds after the fix proves nothing: automate a retry every few minutes.

If nothing works

Compare with another database on the same server where the identity works, or create an empty test database with the same group: if it passes, the problem is local to the database. A failover of the database, or of the elastic pool, forces a fresh execution node; the cause is then an in-memory authentication state, invisible in catalog views. Keep the traces (the server connectivity log) for a possible support ticket.

Lessons learned

Mastodon LLIMastodon SocialFosstodon