question

AtangMogwera-3103 avatar image
0 Votes"
AtangMogwera-3103 asked MikeWright-3648 commented

Login Failed for user ''

Good Day,

I used SQLAdmin group as a set up to azure active Directory admin account. I am a memeber in that group but i am getting issues when i try to access my sql server database through SSMS or azure data studio. I am getting bellow error. 33976-image.png can someone assist?

Login Failed for user '<token-identified principal>'


azure-active-directory
image.png (36.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, Soumi-msft,
I am getting the same error. I am in a security group and the SG has access to the database. I have set the default database in the Options tab, and the phone authentication works, but at the end I get the above error.
Your help is highly appreciated.

Thanks
Xiao Wu
MSFT

0 Votes 0 ·
soumi-MSFT avatar image
3 Votes"
soumi-MSFT answered soumi-MSFT commented

Hello @AtangMogwera-3103, thank you for reaching out. The error "`Microsoft SQL Server, Error: 18456 <token-identified-principal>`" means that the user used to login to SQL Server Management Studio is invalid. It is usually related to an AAD user which is not added on SQL DB that you are trying to connect (User DB or Master DB) or that the AAD user is not the AAD Server Admin.

You just need to add an AAD user in Azure SQL DB. You can follow the steps mentioned here: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities

To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:

 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;

Since you are connecting from SSMS you may also need to change the default database option. 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. Please refer to the screenshot below for more reference:

33963-ssms.png

Hope this helps.

Do let us know if this helps and if there are any more queries around this, please do let us know so that we can help you further. Also, please do not forget to accept the response as an Answer; if the above response helped in answering your query.





ssms.png (19.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @soumi-MSFT. After such much time I spent on investigation it seems like, I am logged on as this user: "domain\name.surname" on my environment. Azure AD user account is my email address: name@domain.com. Isn't the reason I am getting that error: Login failed for user '<token-identified principal>'.

Thank you.

0 Votes 0 ·

Hello @AtangMogwera-3103, yes you are correct. Here you need to use your user's upn which is issued by AAD to that user ie name@domain.com and that should fix this error. Make sure you also add that user to the database as mentioned above.

Also would like to make a small request, in case the above response helped in fixing your issue and also answered your queries, it would be great if you can mark the response as Answer so that it helps others with similar query visiting the forum.

0 Votes 0 ·
RobertKubalski-4995 avatar image
0 Votes"
RobertKubalski-4995 answered

I noticed that such login errors appear when someone changes the name of a database

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikeWright-5141 avatar image
0 Votes"
MikeWright-5141 answered MikeWright-3648 commented

I think the original question was related to logging in as a group member. We're migrating to Azure SQL Managed Instances, and I am trying to grant teams access to the DB via group membership. I have created the group, added it as a DB login, and also added the group to each individual database as "dbowner" (it's a dev DB). However, then the team members log in their receive the same "token identified principal" error.

If logging in via AAD group membership not supported, or am I missing a step?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you set the correct default database in the options tab? I ran into the same issue previously, it was because I spelled the wrong name as the default database.

0 Votes 0 ·

Thanks for the reply. Yes, as the server-level login, I do have a default database selected, and the group is "dbowner" for that database.

0 Votes 0 ·