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

user_894827 0
Viewed 1613

I get the error Login failed for user ''. (Microsoft SQL Server, Error: 18456) from Azure SQL server when a user tries to login using Azure Active Directory - Universal with MFA.

My Azure AD login is within a group, other members of the group can login to the database, but I get the error '<token-identified principal>'. (Microsoft SQL Server, Error: 18456), however If the user is added to the database, then I can login and it works.

create user [myUserName@contoso.com] from external provider
GO
EXEC sp_addrolemember N'db_datareader', N'myUserName@contoso.com'

I was wondering if there is a reason for this.

azure-active-directory azure
1 Answers

The error you are getting is an identical issue its already been raised over Microsoft Q&A Plateform a year ago.

You need to add the users to your SQL DB as AAD Users first to accces the Specifiv SQLServer.

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

Note : Use User UPN(name@domain.com) to access the SQL DB not logged on as this user: "domain\name.surname" on my environment.

user_159693340
Related