This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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()
This error mean the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin
Just create the user in the DB - Create contained database users in your database mapped to Azure AD identities
- 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;
*If you are connecting from SSMS you may also need to change the default database option (Image below). By default it will try to connect to master DB where this user may not exists there as AAD users are contained inside each user database.
