Using Managed Identity Tokens with Azure SQL Connection Pools

This guide is part of Azure SQL Connection Management. A managed identity removes the connection string password: the application asks Entra ID for an access token and presents it when opening a connection. SqlClient supports this in two ways, and the difference matters for pooling. Setting Authentication=Active Directory Default in the connection string lets the driver acquire and cache tokens itself; setting SqlConnection.AccessToken puts the application in control. Both work — until a token is embedded somewhere that becomes part of the pool key, or the application caches a token past its lifetime:

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
  Token is expired.  Error Number:18456, State:1, Class:14

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining
a connection from the pool.

The first is an expired token; the second is often what happens after one — the pool retries, every attempt fails, and callers queue. The objective is a configuration where tokens are fresh, the pool key is constant, and an expiring token never affects an established connection.

Rapid incident diagnosis

Check how authentication is configured. The two mechanisms need different fixes:

grep -rn "Authentication=Active Directory\|AccessToken" --include=*.cs --include=*.json src/ | head

Count the pools. If a token or anything else varies in the connection string, SqlClient creates a pool per variant:

dotnet-counters monitor --process-id $(pgrep -f Orders.Api) \
  --counters Microsoft.Data.SqlClient.EventSource[number-of-pools,number-of-active-connections,active-hard-connections]

number-of-pools above one per database and identity means fragmentation — the failure mode described in Diagnosing SqlClient Connection Pool Fragmentation.

Look at the timing of login failures. Access tokens for Azure SQL are valid for roughly an hour with some variability. Failures clustered at hourly intervals after startup indicate a token cached by the application past its expiry; failures immediately at startup indicate a missing role assignment or a wrong audience.

Verify the database principal exists. The identity must be a contained user in the database:

SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'orders-api';    -- the managed identity's display name

-- create it, from a connection authenticated as an Entra admin
CREATE USER [orders-api] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [orders-api];
ALTER ROLE db_datawriter ADD MEMBER [orders-api];

Check the token’s audience. Azure SQL expects https://database.windows.net/.default; a token minted for another scope fails with the same login error.

Mathematical sizing or parameter formula

Token acquisition rate follows connection creation:

tokens_needed_per_hour ≈ connections_created_per_hour ÷ tokens_cached

With a shared cached token, one acquisition per refresh interval per process is enough regardless of connection rate. Without caching — a token acquired per connection — the rate follows connection churn, and each acquisition is a network call to the identity endpoint unless the SDK’s cache serves it.

Refresh margin. Refresh before expiry, not at it:

refresh_at = expiry − max(5 min, connect_timeout + retry_window)

A five-minute margin covers a slow connect and a retry without ever presenting a token that expires mid-handshake.

Pool sizing is unchanged by the authentication method, but the failure mode during a token problem is a burst of connection attempts:

failed_attempts = pool_max × replicas × retries_per_attempt

Bounding retries keeps a token misconfiguration from hammering both Entra ID and the database.

Identity endpoint throttling. The instance metadata or container identity endpoint serves tokens locally and caches them; the Azure SDK’s DefaultAzureCredential caches in-process as well. Acquiring per connection without any cache can hit endpoint limits during a reconnect burst:

burst_acquisitions = replicas × pool_max        (after a failover or deploy)

Twelve replicas with a pool of 20 is 240 acquisitions within a second or two — comfortably served from cache, and potentially throttled without one.

Exact remediation & configuration

Simplest correct option: let the driver handle it. With Authentication=Active Directory Default, SqlClient acquires and caches tokens through the Azure Identity library, and the connection string contains no secret and never changes:

{
  "ConnectionStrings": {
    "Orders": "Server=tcp:orders-sql.database.windows.net,1433;Database=orders;Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;Connect Timeout=15;Min Pool Size=0;Max Pool Size=40;ConnectRetryCount=3;ConnectRetryInterval=10"
  }
}

builder.Services.AddDbContextPool<OrdersContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("Orders"),
        sql => sql.EnableRetryOnFailure(6, TimeSpan.FromSeconds(15), new[] { 40613, 40197, 49918 })));

Where the application must control the token — a user-assigned identity chosen at runtime, or a federated credential — set AccessToken on the connection and keep the connection string constant:

public sealed class TokenSqlConnectionFactory
{
    private static readonly string[] Scopes = { "https://database.windows.net/.default" };
    private readonly TokenCredential _credential = new DefaultAzureCredential();
    private readonly string _connectionString;
    private AccessToken _cached;
    private readonly SemaphoreSlim _lock = new(1, 1);

    public TokenSqlConnectionFactory(string connectionString) => _connectionString = connectionString;

    private async Task<string> GetTokenAsync(CancellationToken ct)
    {
        if (_cached.ExpiresOn > DateTimeOffset.UtcNow.AddMinutes(5)) return _cached.Token;
        await _lock.WaitAsync(ct);
        try
        {
            if (_cached.ExpiresOn <= DateTimeOffset.UtcNow.AddMinutes(5))
                _cached = await _credential.GetTokenAsync(new TokenRequestContext(Scopes), ct);
            return _cached.Token;
        }
        finally { _lock.Release(); }
    }

    public async Task<SqlConnection> OpenAsync(CancellationToken ct)
    {
        var conn = new SqlConnection(_connectionString)     // constant: one pool
        {
            AccessToken = await GetTokenAsync(ct),          // fresh, not part of the pool key
        };
        await conn.OpenAsync(ct);
        return conn;
    }
}

The single SemaphoreSlim prevents a thundering herd of token acquisitions when many threads notice the expiry at once.

With Entity Framework Core and a custom token, supply the connection rather than the string:

builder.Services.AddDbContext<OrdersContext>((sp, o) =>
{
    var factory = sp.GetRequiredService<TokenSqlConnectionFactory>();
    var conn = factory.OpenAsync(CancellationToken.None).GetAwaiter().GetResult();
    o.UseSqlServer(conn);
});

For pooled contexts, prefer the driver-managed option, because AddDbContextPool expects a stable configuration.

Grant the identity in the database, once, from an Entra-authenticated session:

CREATE USER [orders-api] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [orders-api];
ALTER ROLE db_datawriter ADD MEMBER [orders-api];
GRANT EXECUTE ON SCHEMA::dbo TO [orders-api];

Keep retries bounded and typed. Login failures from an expired token are transient in the sense that a refresh fixes them, but retrying without refreshing is pointless:

catch (SqlException ex) when (ex.Number == 18456)
{
    _cached = default;        // force a refresh on the next attempt
    throw;
}

Do not put the token in the connection string. Password=<token> fragments the pool on every refresh and leaks a credential into anything that logs the string.

Operating identity-based authentication

Watch for hourly error clusters. The clearest signal of a token-handling bug is a small burst of login failures at roughly hourly intervals. An alert on the error number is precise:

- alert: AzureSqlTokenLoginFailures
  expr: increase(app_db_errors_total{number="18456"}[15m]) > 0
  labels: { severity: warning }
  annotations:
    summary: "{{ $labels.service }}: Azure SQL login failures — check token refresh"

Keep the pool count at one. Fragmentation from tokens is silent until the connection count matters; the counter makes it visible:

count by (service) (sqlclient_number_of_pools) > 1

Rehearse an identity change. Rotating to a different managed identity, or adding a user-assigned identity, changes the principal the database must know. Rehearse it in staging: create the new principal, grant roles, switch the configuration, and confirm no window of login failures.

Mind the local development path. DefaultAzureCredential falls back to a developer’s own identity locally, which usually lacks the database roles. Granting a developer group the same database roles, or using a separate local database, avoids the confusing situation where the same code works in Azure and fails on a laptop.

Understand what the token does not do. It authenticates; it does not authorise beyond the database roles granted to the principal, and it does not affect pooling behaviour, throttling limits or session state. Connection limits are still the tier’s, as described in Handling Azure SQL Connection Throttling.

Validation & verification

One pool, one identity. With traffic running, number-of-pools must be 1 per database, and the database must show a single principal:

SELECT login_name, COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY login_name;

Token refresh is invisible. Run the service for two hours under steady load; there must be no login failures and no latency spike at the hourly boundary.

Established connections survive expiry. Open a connection, wait past the token’s expiry without traffic, then use it; it must still work — which confirms that only new connections need a token.

Failures are attributable. Remove the database role in staging and confirm the resulting error names the principal, so the next occurrence is diagnosed in seconds rather than mistaken for a network problem.

Local development works. A developer with the right group membership should be able to run the service against a staging database using their own identity, with the same code path.

Frequently Asked Questions

Related

• Azure SQL Connection Management — the parent guide to Azure SQL’s connection limits and behaviour.
• Configuring Retry Policies for Azure SQL Transient Faults — retrying the errors that surround authentication failures.
• Diagnosing SqlClient Connection Pool Fragmentation — what happens when a token reaches the pool key.
• Tuning Max Pool Size in SqlClient Connection Strings — sizing the pool this page authenticates.