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
- Firewall: does the node egress through an allowed address, or is the "Azure services" option on? Error 40615 states it explicitly.
- Private endpoint: the server name must resolve to a private address from the pool's network (
Resolve-DnsName). Resolving to the public address while public access is off gives a connection failure. - Test the port:
Test-NetConnection <server>.database.windows.net -Port 1433.
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
- Start with the GUID in the message: it tells whether the problem is on the token side or the SQL side.
- Several causes can stack (bad configuration, then a malformed SID, then a pool state): fix one at a time and retest after the wait.
- A token fetched in a test session is not necessarily the task's token: test from the actual node.