AAD Auth Error - Login failed for user ''
Published May 26 2020 03:50 AM 240K Views
Microsoft

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
 
 
  • 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.
 
2020-05-26 11_44_05-Clipboard.png
 
Another sample where this issue can happen is when you typed incorrectly the DB name like sample below. Tks for my colleague Rui Cunha
2022-04-29 09_15_02-Clipboard.png

 

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

FonsecaSergio_0-1671127181952.png

 

Just look for Azure Active Directory

 

FonsecaSergio_1-1671127231671.png

 

You can look for users, groups or Enterprise applications (Service Principals / MSI)

 

FonsecaSergio_4-1671127473071.png

 

Or just use search feature in overview page where you can look for the user found on SQL

FonsecaSergio_2-1671127325820.png

 

In case of Service Principal and Managed Identities look for Application ID instead of Object ID

FonsecaSergio_3-1671127421202.png

 

 

18 Comments
Copper Contributor

What about when you're the server's "Active Directory Admin". i don't have a user created on each database, but i should be able to log in regardless in order to be able to grant access to other AAD Principals...

Microsoft

@JuanParodi in this case (AAD Server Admin) yes, you should be able to login to ANY database. If getting error being AAD Server Admin we would need to investigate further and you can open a case on that

 

Copper Contributor

Any progress on this issue?

Microsoft

@Edvard Gundersen this is not an issue. This is an expected behavior... When you create contained users, in this case was AAD user, but could be SQL login (contained user). You are only able to connect to the specified DB, if you want to connect to master db or to list the available dbs (master metadata) you need to have this user created also on master.

- If user is application user, its not needed any permission on master. And application connection should specify database name

- If user is a SSMS user and if you do not want to always specify the database name you can create user on master DB to have a more simple navigation on SSMS. You do not need any special permission on master, just create user on master should be enough

 

Traditional model Contained database user model
When connected to the master database:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Then when connected to a user database:

CREATE USER 'user_name' FOR LOGIN 'login_name';
When connected to a user database:

CREATE USER user_name WITH PASSWORD = 'strong_password';
Copper Contributor

I'm running into a similar issue with being able to deploy a NEW database onto an Azure SQL Server using token-based authentication from an Azure VM.  I've added the Azure VM to the database server's firewall, I've ensured that the Azure VM has a system-assigned identity.  When I publish the DACPAC to an existing Azure SQL database, there is no problem.  When I try to use the same DACPAC to create a NEW database by simply changing the database name, I get the error message 

 

Login failed foruser '<token-identified principal>'

 

I can't create the contained user before the database is created, obviously.

Copper Contributor

You are probably using a guest invite for someone who has access to multiple Azure tenants. If that's the case, they need to specify the tenant ID as well in the SSMS connection options at the bottom.

 

The tenant ID is xxxx.onmicrosoft.com (the tenant containing the SQL DB)

Copper Contributor

Hi all,

  I'm having an issue when trying to connect an Active Directory user that is the Active Directory Admin over this sql server. 

 

I have been connecting to this database in the past w/out any issues through Management Studio. 

 

However, all of the sudden I am getting this error: login failed for user '<token-identified principal>'

 

Again, the user I'm using is the Active Directory Admin over this instance along w/ the databases within this instance in our Azure environment. 

 

Also, I have tried just connecting to any other database other than the default (Master) db with no success.

 

Does anyone know why it's all the sudden doing this and what the fix is?

Microsoft

@MrGalvan in this case as you said this is Server Admin this issue should not happen. Could be something else. I suggest you to open a case to further investigated it

Copper Contributor

Found the issue and have resolved it. 

 

What happened is the AD group that was assigned as the Active Directory Admin was dropped and created w/ the same name. However, the old group's Object Id wasn't the same as the newly created AD group's Object Id. 

 

Only found this out by looking at the Creation Date of the Active Directory Group. Which shows it was a recent creation date. A time frame within where the issue started happening.

 

So, it appears that the token assigned to the old group was still saved, but that Object Id no logger existed since the group was re-created w/ a new Object Id. 

 

Remedy: Just dropped AD admin and re-added it and now we're back in business. 

Microsoft

@MrGalvan tks for the feedback and for adding the solution to this article. This may help others too

Copper Contributor

You are probably using a guest invite for someone who has access to multiple Azure tenants. If that's the case, they need to specify the tenant ID as well in the SSMS connection options at the bottom.

 

The tenant ID is xxxx.onmicrosoft.com (the tenant containing the SQL DB)

 

This is interestingly not true in the 18.x versions of SSMS 

If you are running SSMS 18.x or later, the AD domain name or tenant ID is no longer needed for guest users because 18.x or later automatically recognizes it.

Funny enough - i login to SQL Managed Instacnes via Guest Accounts via Group Access and I keep getting: 

Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)

 

Only fix i have to switch to Azure Data Studio - works some times OR switch to a VDI - works normally.  My main machine fails often - would  be better to know WHICH creds SSMS attempts to use ... CLI / Powershell / Browser? Need to know where i can go to clear out these creds!

 

 

Microsoft

I get the same error: Login failed for user '<token-identified principal>'. I am able to connect to the database through PowerBI since PowerBI using this database refreshes works fine with my credential from the same machine.  I have tried to download the latest SQL Management Studio and it does not help. I have also downloaded Azure Data Studio, and I run into the same error. Help is highly appreciated. Thank you!

Copper Contributor

@Xiao_Wu - 

what caused my issue was the user was dropped and recreated, so the Object ID in Azure was incorrect. This is what caused the Login failed for user '<token-identified principal>' error message I was receiving. 

 

This same user was also an admin on the sql instance in Azure. So, I had to remove that user as an admin and re-add that same user as the admin. 

 

Once I did that, my problem was resolved. 

 

 

Microsoft

Thank you @MrGalvan for your kind response! 

At the end my issue was due to the default DB I specified has a typo. I will give a suggestion that the error message should be more meaningful to this situation (such as the database you specified cannot be found. ).

 

Thanks

Xiao

Copper Contributor

@Xiao_Wu  - i had the same epiphany  - when i left SSMS and went to ADE my default database was the one variable - sure enough the default database i had was moved off the server so that was my same exact issue. +1 for updating that error message!

Copper Contributor

I have spent number of hours to solve this issue.  

 

If I have user account which is from ExternalIdentity (from different AD), it connects to Database without problem on SSMS but fails to connect via .NET connection string. 

 

I have tried creating Azure Ad group and tried adding that group on the database but same issue.  With azure AD group, I can't able to connect to the database from the internal user.  Really frustrating! [I have added the group members to Azure AD group which I am using to connect]

 

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql...

Copper Contributor

This is not so difficult. I have done it and it works perfectly. But after some time elapses (days or weeks) the error will start throwing up again till I turn managed identity off, and back on again in the web app, then drop and create the user in sql. Obviously I should not have to do this but i’ve had the same issue on two different projects. 

Copper Contributor

Comparing the Object ID against the Client ID from the User Assigned Managed Identity showed that my database user was created with a previous version of the User Assigned Managed Identity. I dropped the UAMI from the database and recreated it, which then solved my issue.

Thanks for pointing me in the right direction!

Co-Authors
Version history
Last update:
‎Aug 10 2023 08:23 PM
Updated by: