ASP.Net Core: 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, 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

My early solution. It can be improved.

Your specific DbContext class

public class MyContext : DbContext
{
    /* 
        * This is an example class
        Your specific DbSets Here
        */

    public MyContext(DbContextOptions options) : base(options) //Important! constructor with DbContextOptions is needed for this solution.
    {
    }
}

Create generic extension method AddDbContext
This method add a factory to ServiceCollections, it creates your DbContext instances with the connection string provided by Func<string> getConnectionStringFunction

static class ServiceCollectionExtensions
{
    public static IServiceCollection AddDbContext<TContext>(this IServiceCollection services, Func<string> getConnectionStringFunction, Action<DbContextOptionsBuilder> dbContextOptionsBuilderAction = null!)
    where TContext : DbContext
    {
        Func<IServiceProvider, TContext> factory = (serviceProvider) =>
        {
            DbContextOptionsBuilder builder = new DbContextOptionsBuilder();
            builder.UseSqlServer(getConnectionStringFunction.Invoke());
            dbContextOptionsBuilderAction.Invoke(builder);
            return (TContext)typeof(TContext).GetConstructor(new Type[] { typeof(DbContextOptions) })!.Invoke(new[] { builder.Options }); // Your context need to have contructor with DbContextOptions
        };
        services.AddScoped(factory);
        return services;
    }
}

In Startup in ConfigureServices

string getConnectionString()
{
    return dbContextSettings.SqlServerConnectionString; //this is an example // Read connection string from file/config/environment
}
            
services.AddDbContext<MyContext>(getConnectionString, builder => builder.EnableDetailedErrors().EnableSensitiveDataLogging());//Dont call UseSqlServer method. It's called from AddDbContext with effective connection string

Controller

[Route("api/[controller]")]
[ApiController]
public class ValuesController : ControllerBase
{
    private readonly MyContext ctx;

    public ValuesController(MyContext ctx)
    {
        this.ctx = ctx;
    }

    // GET: api/<ValuesController>
    [HttpGet]
    public object Get()
    {
        return new
        {
            Instance = $"{ctx.GetType().Name}",
            Provider = $"{ctx.Database.ProviderName}",
            ConnectionString = $"{ctx.Database.GetDbConnection().ConnectionString}"
        };
    }
}

---

Screenshots without restart/rerun application

1st request

My secrets file

{
  "DbContextSettings:SqlServerConnectionString": "Server=localhost;Database=DogsDb;User Id=sa;Password=100;"
}

Screenshot

2nd request without restart the application

I changed the DbName and changed the password with SSMS(Sql Server Management Studio).

Secrets file with the updated connection string

{
  "DbContextSettings:SqlServerConnectionString": "Server=localhost;Database=DeployDB;User Id=sa;Password=1000;"
}

Screenshot