Share via

Azure app service to sql database over system managed identity throw after deployment Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.

Marek Matonok 1 Reputation point
2022-05-06T09:04:21.557+00:00

Hi,

We setup connection between azure app service to sql database over system managed identity.
We used last version of Microsoft.Data.SqlClient (4.1.0) and in connection string : Authentication=Active Directory Managed Identity , that means generation of access token
and caching is already implemented on SqlConnectionProvider in SqlClient.

After deployment we randomly experience below error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.

Sometimes after restart all works. I tried to use msi-validator and also randomly test connection is success or with above error.
Generation of access token is anytime sucessfull.

1) Is any options to see more details in Azure/ Azure Sql Server about that issue?
2) Is this related to incorrect access token?
3) How Sql Server/Database accept/validate access token?
4) Are somewhere any details about overall process from generate access token to validate on Sql Database?
5) How is possible to refresh token?
6) Currently access token expires after 1 day? How I can setup 1 hour expiration?

Thanks,
Marek

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,806 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ryan Hill 26,241 Reputation points Microsoft Employee
    2022-05-19T00:53:27.647+00:00

    Hey @Marek Matonok , I used 4.0.1 package when I enabled Managed Identity on the sample app. I did initially receive that error message; however, it went away when replaced username and password with Authentication=Active Directory Default; and added the app service name as an identity to the table.

    Now I know you indicated this error is intermittent, but is it sporadic or on a consistent interval? How are you using your DbContext across different requests? If you change the authentication to Active Directory Default, does it improve or completely mitigate the issue?

  2. Marek Matonok 1 Reputation point
    2022-06-03T05:32:40.753+00:00

    Hi,

    after we contacted MS support we found out that first we have login error SQL: 18456 with status 5 -> incorrect username/password and after wait status 13 that means our IP address was blocked by SQL Server DoSQuard.

    1) Yes we still can see that error only after full deployment when we remove and create app services, update AAD group and add AAD group to SqlServer. Next day is all fine.
    2) We identified that app service that caused problem is .net framework where I am not able to use Azure Directory Default setting in Connection string. Here after deployment we experience
    also issue with mitting tenantId so first request for access token failed too.

    I think this is a problem with synchronization between create new app service, deploy and start and wait until AAD group is setup after creation and added to SQL server?

    M

    0 comments