Login Failed for user ''

Question

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. can someone assist?

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

Answer 1

Hello @Atang Mogwera , 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://learn.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:

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.

Answer 2

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?

Answer 3

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