Queries regarding the usage of Microsoft.Data.SqlClient AccessTokenCallback in EFCore

[chidhambaram55]

iam referring this example for accessTokenCallback implementation: reference.

Behaviour i was expecting:

My token lifetime is 24hours. For validating whether AccessToken callback changes are working fine, In the AccessTokenCallback Callback function, iam setting expiry as DateTimeOffset.UtcNow.AddMinutes(30), so that i want this method to be invoked every 30 minutes.

Behaviour noticed while testing:

During startup, when different connections are created, 60 logs are found in ConnectionOpeningAsync method. But for only some of them (around 40) AccessTokenCallback is called
After sometime, more new connections are created, due to which again few logs are found in ConnectionOpeningAsync method , due to which Access Token callback function is again invoked for some of these connections.
For same connection which is created around 1hour before, Access Token callback function is not called again even after 30 minutes is passed

Queries:

1. Do we have to pass exact token expiry time? Can we pass other time like 30minutes(Lifetime configured in SqlAuthenticationToken) from now even though token is valid for 24 hours(Actual token lifetime)
2. Will Callback function invoked again by sql client after 30 minutes(Lifetime configured in SqlAuthenticationToken) is passed?
3. When callback function is invoked, will the sql connection id change? Is the previous connection closed and new connection is created when callback function is called?
4. Why in my case, AccessToken Callback is not invoked for few connection opening cases?

Code:

`public class AADAuthDBInterceptor : DbConnectionInterceptor
{
private readonly Func<SqlAuthenticationParameters, CancellationToken, Task> _accessTokenCallback;
private readonly ConfidentialClientAppBuilder _confidentialClientAppBuilder;

public AADAuthDBInterceptor(ConfidentialClientAppBuilder confidentialClientAppBuilder, ILogger<AADAuthDBInterceptor> logger)
{
    _confidentialClientAppBuilder = confidentialClientAppBuilder;
    _accessTokenCallback = AccessTokenCallback;
}

public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
    DbConnection connection,
    ConnectionEventData eventData,
    InterceptionResult result,
    CancellationToken cancellationToken)
{
    var sqlConnection = (SqlConnection)connection;

    var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);

    if (connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && string.IsNullOrEmpty(connectionStringBuilder.UserID))
    {
        sqlConnection.AccessTokenCallback = _accessTokenCallback;
    }

    return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
}

public override InterceptionResult ConnectionOpening(
    DbConnection connection,
    ConnectionEventData eventData,
    InterceptionResult result)
{
    var sqlConnection = (SqlConnection)connection;

    var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);

    if (connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && string.IsNullOrEmpty(connectionStringBuilder.UserID))
    {
        sqlConnection.AccessTokenCallback = _accessTokenCallback;
    }

    return base.ConnectionOpening(connection, eventData, result);
}

private async Task<SqlAuthenticationToken> AccessTokenCallback(SqlAuthenticationParameters sqlAuthenticationParameters, CancellationToken cancellationToken)
{
    var applicationBuilder = _confidentialClientAppBuilder.GetConfidentialClientApp();

    var scopes = new[] { "https://database.windows.net/.default" };
    AuthenticationResult authenticationResult = await applicationBuilder.AcquireTokenForClient(scopes).ExecuteAsync().ConfigureAwait(false);

    return new SqlAuthenticationToken(authenticationResult.AccessToken, DateTimeOffset.UtcNow.AddMinutes(30));

}

}`

[David-Engel]

The token will also be part of the connection pool. So if you haven't disabled pooling and as long as a pool is active and the token isn't expired, there will be no need to request a token from the callback.

1. You can pass whatever expiry time you want. That's what SqlClient will go by.
2. The callback will be invoked when the first connection in a new connection pool is made, when a new physical connection is made in a pool for which the existing/previous token expires within 10 minutes, or when a physical connection is opened without pooling.
3. The callback will only ever be invoked when a new physical connection is being made, thus it will have a new connection id.
4. Connection pooling.