Can no longer connect to Azure SQL Database

Andrew Fraser 96

I have a database client application running on Windows with the option to connect to three separate databases on Azure. Each database has a separate client logon credentials. This client application and databases have been working great for several years. However, today when I try and connect to any of the databases I get a "'Login failed for user.....". This would appear to indicate that it is not accepting the password for all three of these database logins. Nothing that I am aware of has changed recently. Has something changed on Azure SQL databases that I don't know about?

Andy

Oury Ba-MSFT 18,946 Reputation points Microsoft Employee

2024-08-23T22:45:05.5833333+00:00
@Andrew Fraser Thank you for reaching out.

Could you please share the complete error message.?

Are you login through SSMS?

Login failed for user ....

If the error is

"Microsoft SQL Server, Error: 18456 <token-identified-principal>"

It 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:

Copy

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;

https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535

Regards,

Oury
Oury Ba-MSFT 18,946 Reputation points Microsoft Employee

2024-08-27T16:31:31.7866667+00:00

@Andrew Fraser We haven't heard back from you since my last comment. Could you please share the status of the issue.

Regards,

Oury
Andrew Fraser 96 Reputation points

2024-08-27T16:38:34.2266667+00:00

Sorry for the late response - I did not get an email notification to your reply :-(

So this issue was happening when connecting to the database(s) with any tool including my client application. This application has been running for years without problem. It is as if the credentials have been automatically expired. I basically had to create new logins for each of the databases and update my connection strings and re-issue an update of the client to my users.

The point of this is that I did nothing to the database and suddenly out of the Blue, none of my users could connect to the database from my client application and I would like to know why so I can stop it happening again.
Andrew Fraser 96 Reputation points

2024-08-27T16:44:46.7133333+00:00

I forgot to add, the error message was:

"The server principal is not able to access the database under the current security context."

Andy
Oury Ba-MSFT 18,946 Reputation points Microsoft Employee

2024-08-30T22:46:10.9433333+00:00

Andrew Fraser

Could you please open a support case so we can better investigate this.

Regards,

Oury
Oury Ba-MSFT 18,946 Reputation points Microsoft Employee

2024-09-03T16:50:47.4066667+00:00

Andrew Fraser

Were you able to create a support ticket.?

Regards,

Oury
Andrew Fraser 96 Reputation points

2024-09-03T17:20:37.7166667+00:00

@Oury Bah No, I have not had the time and have had to move on with my application and am no longer in a position to replicate the issue.

Andy

Share via

Can no longer connect to Azure SQL Database

Your answer