AAD Auth Error - Login failed for user '<token-identified principal>'
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 in the database that you are trying to connect, 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. Another sample where this issue can happen is when you typed incorrectly the DB name like sample below. Tks for my colleague Rui Cunha 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 IDMigrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio
In this blog post, I will demonstrate how to use the Azure Migration extension of Azure Data Studio to migrate AWS RDS SQL Server database to Azure SQL Database offline. The Azure SQL migration extension for Azure Data Studio enables you to assess, get right-sized Azure recommendations and migrate your SQL Server databases to Azure.
Can Certain SQL User Roles Avoid Blocking?
We recently came across a case where blocking was being observed when performing a DDL operation, while querying system views concurrently. However, performing the same query with a different user was not triggering the same blocking pattern. In this article, we will reproduce the same steps and briefly go through the reason why specific user roles may avoid blocking in certain scenarios.Azure SQL Learning Opportunities
Want to learn more about Azure SQL? Starting on Wednesday, August 5th, Microsoft’s product learning team will be introducing 60 new sessions across multiple platforms to help everyone learn Azure SQL. These platforms will be YouTube, GitHub, Channel 9, and Microsoft Learn. Along with launching new content in August, they're also hosting a LIVE bootcamp over the course of four days (August 17 – 20) where you can learn Azure SQL from Anna Hoffman and Bob Ward and get your questions answered. Each day, there will be a two-hour live session for you to join. Each session will be run live from 9AM-11AM PDT / 12PM-2PM EST. After or during the session, there will also be hands-on labs to complete. By the end of the week, you should be comfortable with the Azure SQL fundamentals (or the "meat and potatoes" of Azure SQL). I personally will be attending. You can register for the bootcamp on Channel 9 here. Finally, you can take an in-depth look at Azure SQL Database by downloading this free eBook: Professional Azure SQL Database Administration. Don’t forget to follow along with the learning community @AzureSQL on Twitter or the #AzureSQL hashtag.
