Azure SQL Database: Managed identity login fails with error 18456

Question

I have a user-assigned managed identity with an existing contained database user in an Azure SQL Database that cannot log in via Azure AD authentication. Every attempt fails with a login error naming a token-identified principal as unable to log in. I found that the database user had originally been created using a name-based external provider lookup, which stored the identity's Application ID as the login identifier instead of its Object ID, so the two never matched. I fixed this by recreating the user with an explicit identifier derived from the correct Object ID, and confirmed via a follow-up query that the stored value now matches exactly. Despite that, the login still fails with the identical error, both immediately after the fix and again after waiting over twelve hours to rule out a backend propagation delay. I have more troubleshooting detail to add but am testing in stages due to a posting error. The setup: this is a user-assigned managed identity attached to an Azure VM, authenticating via the ActiveDirectoryManagedIdentity method. I've tested this both via sqlcmd and via a Node.js script using ManagedIdentityCredential with the mssql driver's azure-active-directory-access-token auth type, and both fail identically.

To dig further, I decoded the identity's actual live AAD token directly from the instance metadata service. Its object ID and subject claims both exactly match the corrected identifier I set on the database user. The application ID, audience, tenant ID, and issuer all look correct and well formed. So the token is unambiguously right, and the identifier it should be matched against is unambiguously right, and they match each other exactly, yet the login still fails.

I've also ruled out a wrong database name (retested against the confirmed correct name), identity ambiguity (the VM has only this one managed identity, no system-assigned identity that a client tool could be authenticating as instead), and relevant server-level configuration (mixed-mode AAD authentication is enabled, not AAD-only). No historical audit or diagnostic logs were available to check for a more specific server-side error code, since both SQL Auditing and Azure Monitor diagnostic settings were disabled on this database. I also ran the Azure Portal's built-in error 18456 diagnostics tool covering the full window of failed attempts, with authentication method set to Managed Identity. The result was that the system is good, with no issue detected.

Given a provably correct identifier mapping, a provably matching token, and clean automated diagnostics, I'm not sure what else to check from the client side. Has anyone seen a managed identity login fail like this even when the stored identifier and the token's object ID are confirmed identical? Is there a known caching layer, beyond normal propagation delay since twelve-plus hours has already elapsed, that could hold onto a stale principal mapping at the SQL gateway level, or anything else that would not be visible from the client side? I have a formal support case in progress for this but was directed here first per the support plan's request process.

Answer 1

The one area I'd suggest double-checking is the actual stored SID bytes rather than the GUID string:

SELECT name, type_desc, authentication_type_desc,
       sid, DATALENGTH(sid) AS sid_len
FROM sys.database_principals
WHERE name = 'your_user_name';

The most common cause that survives a "the identifier matches" check is GUID byte-order. When a SID is built by hand with WITH SID = 0x..., the first three components of the Object ID GUID need to be byte-swapped (little-endian). If the GUID was converted in string order instead, the stored SID ends up a scrambled version of the correct GUID and a verification that compared the intended values rather than the actual stored bytes would still look like a match. That would fit your symptoms nicely: a correct-looking identifier but a persistently failing login.

The most reliable test is a direct byte-level comparison, the token's Object ID converted to a SID versus the raw sid from the query above. If those differ, that's likely your answer.

If it turns out to be the byte-order issue, the cleanest fix avoids hand-building a SID at all:

DROP USER [your_user_name];
CREATE USER [your_user_name] FROM EXTERNAL PROVIDER WITH OBJECT_ID = 'the-object-id-guid';

WITH OBJECT_ID is the supported path for user-assigned managed identities, it skips the name-based Graph lookup that stored the Application ID originally, and lets Azure SQL construct the SID correctly on its own.