An error may appear when trying to login to Azure SQL DB using AAD authentication
===================================
Cannot connect to SERVENAME.database.windows.net.
===================================
Login failed for user '<token-identified principal>'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&Evtsrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: SERVENAME.database.windows.net
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
- CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
- CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
- CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
- CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
- CREATE USER [appName] FROM EXTERNAL PROVIDER;
Another scenario that happened on some customer is where a user / group does not match with SQL by ID. As sample when user was deleted and recreated on AAD with same name. Even though same name you need to make sure SQL User SID match the AAD Object ID or Application ID (In case of Service Principal or MSI).
To fix it you will need to recreate user and grant required permissions
To verify if this is the issue run query below in the database you are trying to connect
--SID to OBJECTID
SELECT
DP.name
,DP.principal_id
,DP.type
,DP.type_desc
,DP.SID
,OBJECTID = CONVERT(uniqueidentifier, DP.SID)
FROM SYS.database_principals DP
WHERE DP.type IN ('S','X','E')
And compare results with AAD object that you can check on Azure Portal
Just look for Azure Active Directory
You can look for users, groups or Enterprise applications (Service Principals / MSI)
Or just use search feature in overview page where you can look for the user found on SQL
In case of Service Principal and Managed Identities look for Application ID instead of Object ID
Microsoft
