CODEWITHSUNDEEP
c#azure-managed-identitysqlclient
Nikita Christie
Nikita Christie

Reputation: 1

SQL access with Managed Identity - token expiry

We have an app service that connects to Azure SQL using MI.

Ever since we migrated to using MI, we are seeing this error:

An unhandled exception occured while processing a job: Microsoft.Data.SqlClient.SqlException (0x80131904): Next reconnection attempt will exceed query timeout. Reconnection was terminated.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'. Token is expired.
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.ReplaceConnection(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

The error goes away after app restart and seems to recur after few days.

We have a DbConnectionInterceptor which gets the token as part of ConnectionOpening() method.

App builder initialization:


var applicationBuilder = ConfidentialClientApplicationBuilder
            .Create(clientId)
            .WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
            .WithClientAssertion(new ManagedIdentityClientAssertion(managedIdentityClientId).GetSignedAssertion)
            .WithAuthority(AzureCloudInstance.AzurePublic, tenantId)
            .WithAzureRegion(ConfidentialClientApplication.AttemptRegionDiscovery)
            .Build();

Get token method called from ConnectionOpening method:

private string GetAccessToken()
{
    var applicationBuilder = _confidentialClientAppBuilder.GetConfidentialClientApp();
    var scopes = new[] { "https://database.windows.net/.default" };
    AuthenticationResult authenticationResult = Task.Run(async () => await applicationBuilder.AcquireTokenForClient(scopes).ExecuteAsync().ConfigureAwait(false)).GetAwaiter().GetResult();

    return authenticationResult.AccessToken;
}

Is token refresh not taken care of internally? Is there anything extra we need to do here? We have another application that is not using DbContext pooling which seems to be working fine. Only the one that uses pooling is facing this issue. Any insights would be helpful.

Upvotes: 0

Views: 115

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8402

Is token refresh not taken care of internally? Is there anything extra we need to do here?

As per documentation

The DefaultAzureCredential class caches the token in memory and retrieves it from Microsoft Entra ID just before expiration. You don't need any custom code to refresh the token.

This is but present in System.Data.SqlClient (on .NET Framework) where, in certain scenarios, when a token expires for a connection in a connection pool, SqlClient can fail to discard the connection and refresh the token

Use Microsoft.Data.SqlClient this client often handles token expiration and Managed Identity better, with improved support for AAD token refresh. To resolve the issue in your code, add this NuGet package:

Install-Package Microsoft.Data.SqlClient -Version 5.1.0

After installing, update your code to use Microsoft.Data.SqlClient

please check this document for more information.

Upvotes: 0

Related Questions