Transient Login errors for Azure SQL Database-CodePudding

We are hosting a .NET5 WebApi (ASP.NET Core) using Entity Framework 6.4.4 (not core) and connecting to Azure SQL Database.

Every once in a while, calls to our API fail because of this Exception:

The underlying provider failed on Open. Login failed for user 'myuser'.

We know that we have the correct user and password because the error goes away on its own after a few seconds. We are using SqlAzureExecutionStrategy, but this is not retrying that specific error code (18456).

What would be a good way to solve this issue? Should we simply retry this error code as well, or is there a better solution?

We currently set the following configuration:

[DbConfigurationType(typeof(DatabaseConfiguration))]
public class MyDataContext: DbContext, IMyContext
{...}

public class DatabaseConfiguration : DbConfiguration
{
    public DatabaseConfiguration()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
        SetDefaultConnectionFactory(new LocalDbConnectionFactory("mssqllocaldb"));
        SetProviderServices("System.Data.SqlClient", SqlProviderServices.Instance);
    }
}

CodePudding user response：

My own best guess for a wordaround would be to extend the execution strategy.

I found a somewhat similar discussion on GitHub, but it concerns AAD authentication instead of username/password.

    public class CustomExecutionStrategy : SqlAzureExecutionStrategy
    {
        protected override bool ShouldRetryOn(Exception ex)
        {
            if (ex is SqlException sqlex)
            {
                foreach (SqlError err in sqlex.Errors)
                {
                    // Error number found here https://github.com/dotnet/SqlClient/issues/617#issuecomment-649686544
                    if (err.Number == 18456)
                    {
                        return true;
                    }
                }
            }

            return base.ShouldRetryOn(ex);
        }
    }