Troubleshoot Azure SQL Error 18456 for Microsoft Entra Group Users

If a Microsoft Entra server login is removed from the logical master database in Azure SQL Database, any database users linked to that login become orphaned. These orphaned users silently fail to authenticate, resulting in Error 18456 (internal state 5). This problem can stay hidden for an extensive period, especially if an Entra admin account covers up the broken group path. This article will help you understand how to identify, diagnose, and fix this issue.

Azure SQL Database provides two methods for creating Microsoft Entra users: contained database users and login-based users. Both methods are effective, yet they come with different dependency chains. Recognising this distinction is vital because choosing the incorrect model or breaking its dependencies can lead to authentication problems that are tricky to identify and often overlooked.

We’ll detail a real-life situation where Microsoft Entra groups were set up as login-based database users. When the corresponding server logins were later deleted from the logical master database, these database users became orphaned. The resulting Error 18456 went unnoticed because the primary administrator was also the Entra admin on the server, a role that bypasses group-based principal resolution altogether.

In this guide, we’ll examine the symptoms, the investigation process, identify the root cause, propose solutions, and offer best practices for prevention.

Architecture Overview:

• Azure SQL Database (single database, General Purpose tier)
• Multiple logical servers hosting various user databases
• Microsoft Entra ID authentication is enabled
• Entra security groups are utilised for role-based database access (e.g., “High Risk”, “Low Risk” developer groups)
• One user is configured as both a member of an Entra group and also serves as the Entra admin on the server

Database Principal Setup:

Some databases had Entra group principals established as contained database users (the best practice), while others had the same groups set up as login-based users. This inconsistency was caused by using different provisioning tools or scripts at different times.

These two types of principals generate different SID (Security Identifier) formats in sys.database_principals:

Client-Side Error Example (SSMS):

Login failed for user ”. (Microsoft SQL Server, Error: 18456)

Symptoms Observed:

• Users who are members of a Microsoft Entra security group encounter Error 18456 when trying to access certain databases.
• Those same users can access other databases on the same or different logical servers without any issues.
• If the user is appointed as the Entra admin on the server temporarily, they can successfully connect to all databases.
• The problem affects all members of the group, not just a single user.
• Clearing the browser’s token cache or re-authenticating does not resolve the problem.

Key Distinction:

The Entra admin can access the database (mapping to ‘dbo’), but the same user cannot connect solely through group membership once the admin role is revoked.

To investigate, connect to the affected user database as the Entra admin and execute:

SELECT name, type_desc, DATALENGTH(sid) AS sid_length, sys.fn_varbintohexstr(sid) AS sid_hex, 
CASE WHEN DATALENGTH(sid) = 16 THEN CONVERT(uniqueidentifier, sid) 
WHEN DATALENGTH(sid) = 18 THEN CONVERT(uniqueidentifier, SUBSTRING(sid, 1, 16)) 
ELSE NULL END AS entra_object_id, 
CASE WHEN DATALENGTH(sid) = 18 AND RIGHT(CONVERT(varchar(170), sid, 2), 4) = 'AADE' THEN 'LOGIN-BASED' ELSE 'CONTAINED' END AS mapping_model 
FROM sys.database_principals WHERE type IN ('E', 'X');

What to Look For: Any principal displayed as LOGIN-BASED with an 18-byte SID is a candidate for this issue.

Next, connect to the logical master database and run:

SELECT name, type_desc, DATALENGTH(sid) AS sid_length, sys.fn_varbintohexstr(sid) AS sid_hex, 
CASE WHEN DATALENGTH(sid) = 16 THEN CONVERT(uniqueidentifier, sid) ELSE NULL END AS entra_object_id 
FROM sys.server_principals WHERE type IN ('E', 'X');

What to Look For: If a login-based database user exists but there’s no corresponding server principal in this query (matching by name or first 16 SID bytes), it indicates the database user is orphaned.

Run this query in the affected database to check how the current connection resolves:

SELECT @@SERVERNAME AS connected_server, DB_NAME() AS connected_database, ORIGINAL_LOGIN() AS original_login, SUSER_SNAME() AS server_identity, USER_NAME() AS database_identity;

If database_identity returns ‘dbo’, it’s using the Entra admin identity, not the group identity. Any IS_MEMBER() checks from this session will yield 0 for Entra groups since the ‘dbo’ identity does not enable group membership resolution. This is normal behaviour and not an indication that the user is not a group member.

SELECT principal_id, sid, name, type, usage FROM sys.user_token ORDER BY principal_id;
SELECT IS_MEMBER(N'') AS is_group_member;

Important: These queries must be executed from a non-admin session to yield accurate results. An Entra admin session always maps to ‘dbo’ and fails to resolve any group principals in the token.

Run the first Step 1 query on both a database where access is granted and one where it is denied. Compare the mapping_model column for the same group name.

The Microsoft Entra group was initially set up as a login-based database user across various databases. This occurred by first creating a server login using CREATE LOGIN [...] FROM EXTERNAL PROVIDER on the logical master, followed by the creation of the database user with CREATE USER [...] FROM LOGIN [...] in each user database. This resulted in an 18-byte SID (the Entra group’s object ID plus the AADE marker).

At some point post-creation, the server login was removed from the logical master (either manually or automatically), while the login-based database users stayed intact. Consequently, these database users became orphaned, attempting to reference a login that no longer exists, leading Azure SQL to fail in resolving the authentication path.

When a user tries to authenticate via an Entra group containing an orphaned login-based database user, the engine goes through the following steps:

1. Receives the Entra token with the group’s object ID
2. Finds the login-based database user (18-byte SID with AADE)
3. Attempts to find the corresponding server login
4. Finds no matching server login in master
5. Rejects the connection with Error 18456, internal state 5

The issue remained concealed because the primary user testing access also held the role of Entra admin on the server. The Entra admin functions as ‘dbo’ in all databases, skipping the resolution of group principals. The orphaned login-based group went unused through the regular authentication pathway until a non-admin connection was made.

For each affected database, follow these steps while connected as the Entra admin:

SELECT rp.name AS role_name 
FROM sys.database_role_members drm 
JOIN sys.database_principals dp ON dp.principal_id = drm.member_principal_id 
JOIN sys.database_principals rp ON rp.principal_id = drm.role_principal_id 
WHERE dp.name = N'';

SELECT state_desc, permission_name, class_desc, OBJECT_NAME(major_id) AS object_name 
FROM sys.database_permissions 
WHERE grantee_principal_id = ( SELECT principal_id FROM sys.database_principals WHERE name = N'' );

DROP USER []; 
CREATE USER [] FROM EXTERNAL PROVIDER;

-- Reassign roles (modify according to Step 1 output) 
ALTER ROLE db_datareader ADD MEMBER []; 
ALTER ROLE db_datawriter ADD MEMBER []; 
ALTER ROLE db_ddladmin ADD MEMBER []; 
-- Reassign any explicit permissions from Step 2 output

DBCC FLUSHAUTHCACHE; 
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');

Note: Step 5 is crucial. Azure SQL maintains a cache of authentication decisions, and the stale cached entry for the old login-based principal may persist even after the principal has been recreated.

SELECT name, DATALENGTH(sid) AS sid_length, 
CASE WHEN DATALENGTH(sid) = 18 AND RIGHT(CONVERT(varchar(170), sid, 2), 4) = 'AADE' THEN 'LOGIN-BASED (problem)' 
ELSE 'CONTAINED (OK)' END AS status, 
CONVERT(uniqueidentifier, sid) AS entra_object_id 
FROM sys.database_principals 
WHERE name = N'';

Expected Result: sid_length should equal 16, status should be CONTAINED (OK), and entra_object_id should correspond to the Entra group’s object ID.

1. Remove the test user as Entra admin (or select a different group member who is not an admin).
2. Access the database using SSMS with Microsoft Entra MFA authentication.
3. Execute the following:

SELECT USER_NAME() AS database_identity, IS_MEMBER(N'') AS is_group_member;

Expected Outcome: database_identity should not be ‘dbo’, and is_group_member should equal 1.

SELECT DB_NAME() AS database_name, name, type_desc, DATALENGTH(sid) AS sid_length, 
sys.fn_varbintohexstr(sid) AS sid_hex, 
CASE WHEN DATALENGTH(sid) = 18 AND RIGHT(CONVERT(varchar(170), sid, 2), 4) = 'AADE' THEN 'LOGIN-BASED (needs fix)' 
ELSE 'CONTAINED (OK)' END AS status 
FROM sys.database_principals 
WHERE type IN ('E', 'X');

1. Always utilise CREATE USER [...] FROM EXTERNAL PROVIDER for Entra principals. This sets up contained database users with a 16-byte SID that operates independently of any server login. Only use CREATE USER [...] FROM LOGIN [...] if there’s a specific need for server-level login behaviours and if you understand the dependencies involved.
2. Avoid deleting server logins before converting their login-based database users to contained users. Removing a server login orphans all associated login-based users mapped to it across every database on that server.
3. Don’t rely on Entra admin access to confirm group-based authentication. The Entra admin session maps to ‘dbo’ in every database, hence skipping the group principal resolution. Always conduct your tests with a non-admin group member.
4. Standardise your provisioning scripts. Different teams or deployment processes using diverse CREATE USER methods create an inconsistent mix of contained and login-based principals that are hard to manage.
5. Regularly audit Entra principals across all databases. Use the validation query provided to identify login-based principals and take proactive measures to convert them before they lead to access issues.
6. Flush authentication caches after making principal model changes. After removing and recreating an Entra principal, execute DBCC FLUSHAUTHCACHE and DBCC FREESYSTEMCACHE('TokenAndPermUserStore') to clear aged cached entries.

• IS_MEMBER() outputs 0 for Entra admin sessions. This outcome is expected because the admin session identifies as ‘dbo’ and not as the group user. So, don’t consider IS_MEMBER() = 0 as proof of a user’s absence from the Entra group.
• TRY_CONVERT(uniqueidentifier, sid) produces NULL for 18-byte SIDs. This is expected behaviour for login-based users and does not suggest SID corruption. To extract the Entra object ID from an 18-byte SID, use CONVERT(uniqueidentifier, SUBSTRING(sid, 1, 16)).
• The AADE suffix is a documented marker, not a sign of corruption. Microsoft states that login-based Entra database users will have a SID made up of the Entra object ID bytes along with the AADE marker.
• USE [database] is not functional in Azure SQL Database. You can’t switch databases in the same session. Make sure to open separate connections or modify the database in SSMS Connection Properties to run diagnostics on both master and user databases.
• Deleting and recreating a user will not retain role memberships or explicit permissions. Always document roles and permissions before removing any principal.

This situation highlights how a seemingly simple action, such as deleting a server login, can result in an unnoticeable authentication problem that persists for years. The combination of login-based Entra principals, a missing server login, and the overlay of Entra admin privileges makes this issue particularly difficult to uncover.

The solution is quite straightforward: recreate the impacted principals as contained database users and clear the authentication cache. More importantly, the key lesson here is to implement preventive measures: standardise the use of contained Entra database users, never remove server logins without converting their connected users, and always verify group-based access through a non-admin identity.