Hi, looking for some insights on the best solution for our scenario.

We have an app service that uses Managed Identity to connect to an Azure SQL Db which is in another tenant. When moving from SQL user/pwd to MI, we needed custom Auth code, so we used DbConnectionInterceptor for our DbContextPool to create and assign token in ConnectionOpeningAsync. Post this implementation, we are facing token expiry issues intermittently. To fix this, we are exploring AccessTokenCallback.

Implementation will look like this:

public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
 DbConnection connection,
 ConnectionEventData eventData,
 InterceptionResult result,
 CancellationToken cancellationToken)
{
 var sqlConnection = (SqlConnection)connection;
if (sqlConnection.AccessTokenCallback == null)
{
 sqlConnection.AccessTokenCallback = async (parameters, token) =>
 {
 var accessToken = await GetAzureSqlAccessTokenAsync();
 return new SqlAuthenticationToken(accessToken, DateTimeOffset.UtcNow.AddHours(1));
 };
}
 return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
}

According to the documentation (link), since the callback method is part of the pool key, if the callback method is not static, it will create a new pool for every connection. Our token provider is not static. Does this mean that if we implement this, for every connection there will be a separate pool? What does this mean for the performance of the service?

We also see that SqlAuthenticationProvider is another way to implement custom Authentication logic. Is this preferred over AccessTokenCallback? We are using AddDbContextPool to configure. Is there any sample code on how to configure this with AddDbContextPool?