System.Data.SqlClient.SqlException: Login failed for user '

Question

I have an ASP MVC app on Azure App Service.

The app uses Managed Identity to connect Azure SQL database.

All is working correctly, but from time to time app stops working and I have an error

System.Data.SqlClient.SqlException: Login failed for user '

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Possible Root Causes:

1. Token Expiry or Refresh Delay: Managed Identity uses tokens to authenticate with Azure SQL DB. These tokens have an expiration time (usually 1 hour). If the token expires and there is a delay in refreshing or acquiring a new one, you may experience this intermittent failure.
2. Azure AD Service Issues: The authentication between the Azure App Service and Azure SQL DB relies on AAD. If AAD has transient issues (e.g., temporary unavailability or latency), it can cause failures in obtaining or validating the token.
3. Connection Pooling and Token Caching: If your app uses connection pooling (as most do by default), expired tokens might be cached in the connection pool. When an old token is used, authentication will fail.
4. Service-Specific Issues: Azure App Service or Azure SQL Database may experience regional service degradation or disruptions, causing intermittent connectivity problems.
5. Misconfigured Identity or Database Access: While less likely since it works most of the time, issues like temporary loss of permissions or a configuration drift might result in intermittent failures.

Probable Fix:

1. Verify that your app is using the latest version of the Microsoft.Data.SqlClient library (not System.Data.SqlClient). The newer library has better support for Managed Identity and token refresh.
2. Add proper retry logic to your app for transient errors, including retries for failed token acquisitions.
3. Use the RetryLogic feature in Microsoft.Data.SqlClient to automatically retry transient failures.
4. Clear and refresh connection pools when tokens expire to avoid using expired tokens.
5. If this issue occurs during high-load periods, consider increasing the token refresh frequency by implementing custom token acquisition and refresh logic.
6. Confirm that the Managed Identity assigned to the app service has the necessary roles (e.g., db_datareader, db_datawriter) on the Azure SQL DB.
7. Ensure the app's IP or region isn't being temporarily blocked due to changes in the firewall settings.

Best Practices

• Use connection resiliency patterns: Implement retry logic for database connections and transient errors.
• Regularly update libraries: Use the latest version of the Microsoft.Data.SqlClient package.
• Monitor system load: Ensure the Azure App Service and SQL Database have sufficient resources during high-demand periods.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.