Database connection fails when the password is changed, but works when the application is restarted

Question

We have an ASP.Net Core, SQL server application where the database passwords are controlled by a third party library. The passwords get changed(every 75 to 90 days without notice), when the application is running.

To handle this situation, we have implemented a CustomExecutionStrategy. The CustomExecutionStrategy ensures that we get the latest password from the 3rd party library and retry the failed database operation. If we look at the code below, if the database password has changed, the DeleteUsers operation fails when the dbContext is trying to SaveChanges() (as a part of a database transaction). If however we restart the application, then the same code works fine.

What could I be missing?

service where code is failing:

public bool Deleteusers(List<string> usernames)
{
    var strategy = _dbContext.Database.CreateExecutionStrategy();
    var connectionsyring=_dbContext.Database.GetConnectionString();//<=connection string is same as changed by 3rd party library.
    var strategyDelete=strategy.Execute(()=>
    {
        using (var transaction = _dbcontext.Database.BeginTransaction())
        {
            //Call _dbcontext.SaveChanges() after making changes<=Code Fails
            transaction.Commit();
        }
    }
    return strategyDelete;
}

Startup class:

protected override void ConfigureDbContext(IServicecollection services)
{
  services.AddDbContext<SecurityDbContext>(options=>options.UseSqlServer (<Connectionstring>,sqlserveroptions => sqlserveroptions.CommandTimeout(100)));
}

Startup base class, from which actual startup class inherites:

public void ConfigureServices(IServiceCollection services)
{
  services.AddControllers(); 
  services.AddDbContext<OrdersContext>(options =>
  {
    options.UseSqlServer(Configuration.GetConnectionString("OrdersDatabase"),
      sqlServerOptionsAction: sqlOptions =>
      {
        sqlOptions.ExecutionStrategy(x => 
          new CustomExecutionStrategy(x, 10, TimeSpan.FromSeconds(10)));
        sqlOptions.CommandTimeout(_conninfo.ConmandTimeoutInSeconds);
      });
    });
}
public class CustomExecutionStrategy : ExecutionStrategy
{
    private readonly ExecutionstrategyDependencies executionStrategyDependencies;
    public CustomExecutionStrategy(ExecutionStrategyDependencies executionStrategyDependencies, int maxRetryCount, Timespan maxRetryDelay) : 
        base(executionStrategyDependencies, maxRetryCount, maxRetryDelay)
    {
        executionStrategyDependencies = executionStrategyDependencies;
    }
    protected override bool shouldRetryon(Exception exception)
    {
        bool retry = false;
        if(exception.GetType() == typeof (Microsoft.Data.SqlClient.Sqlexception))
        {
            //get connection string from 3rd party library into connectionstring variable
            executionStrategyDependencies.currentContext.Context.Database.SetConnectionstring(connectionstring);
            retry=true;
        }
        return retry;
    }
  }

Answer 1

@Anonymous , this is not true, please see microsoft's documentation here.

Answer 2

Hello @Anonymous , Thank you for your detailed response. I have added line 4 at code section 1.

"But in your scenario, you are changing the connection string, as mentioned in my comment, the startup code runs once when the application starts, it will get the connection string and store it in memory, after that when you want to access the database, it will get the connection string from memory and use it. Then, if you change the database password, the connection string in the memory not changed, so, you must restart the application to update the connection string" =>

If you check line 4 at code section 1, I am getting the database connection string (before using it in the database transaction) and it is the same as the changed database connection string(code section 3, line 29). This code works in 2 other applications which do not use database transactions and where the database password is changed without the application being restarted.

Please let me know if you have any further inputs

Answer 3

Thank you @Anonymous , I will open a ticket for the EF Core team.