Entra ID Groups access to Azure SQL DB - Sporadic & Extended Connectivity Failures

walkerm 20 Reputation points
2025-05-22T16:19:23.3666667+00:00

I have three Entra Groups configured that contain both users and a System Managed Identities (SMI) from a Synapse Workspace. These entra groups have been added as SQL Users with the appropriate permissions to an Azure SQL DB. I have been running Synapse Pipelines and Power BI Refreshes off this DB using Entra Users and the SMI for 2 years now with no issues. The users have the correct SQL roles and have been able to log in historically.

Seemingly without a pattern the Entra Groups will loose all access to the SQL DB. The only Entra user that can seemingly access the database is the Entra Admin. All of the groups loose access for both users and for the SMI. I created a new SQL user with the same permissions as the Entra Groups and that worked fine but the Entra Groups lost access, despite still displaying in the SQL server as active users with the correct roles. Then after 3 days of no access, I tested the Entra Group access again, and it worked just fine.

An added layer of confusion the same Entra Groups that lost access to one Azure SQL Server and DB, also had access to a separate one, and that authentication and connectivity was working just fine during the same time frame the group was unable to access the problematic DB. This makes me think it was not a configuration issue with the Entra Group.

The error I was receiving in Synapse from the SQL Server was as follows:

''Type=System.Data.SqlClient.SqlException,Message=Login failed for user 'xxxxxx'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=18456,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=18456,State=1,Message=Login failed for user 'xxxxxx'.,},],'

Has anyone else experienced a similar issue and has anyone found the cause?

Azure SQL Database

Answer accepted by question author

Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator
2025-05-23T18:06:40.2233333+00:00

Hi walkerm,

If the issue suddenly appears and you require immediate mitigation, please attempt running DBCC FREESYSTEMCACHE(‘TokenANDPermUserStore’) and DBCC FLUSHAUTHCACHE on the affected database. In some rare instances, an issue with the security context cache may affect verification of Entra tokens.

Have you tried this approach yet? If so, did it help resolve the issue? If not, give it a try and let me know how it goes!

I hope this information helps. Please do let us know if you have any further queries.

Was this answer helpful?

2 people found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Simon Burbery 736 Reputation points
    2025-10-20T03:45:37.6466667+00:00

    We had groups of type "Microsoft 365" assigned access to SQL Database. 3 days ago the users lost access. When deleting the groups and trying to add them again, I received error "principal type not supported". We created new groups of type "Security" and added them to resolve the issue. I am trying to find documentation that explains why this type of group lost access on 17th/18th October 25.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.