Entra ID Managed Identity versus SQL Login Password Expiration for Least-Privilege Azure SQL Access

Direct answer

Entra ID managed identities cause authentication to fail at the next connection open after the OAuth token expires; SQL logins with CHECK_EXPIRATION ON cause failure at the next login attempt after the password expires. In a pooled‑connection scenario, a connection that was opened before expiry stays usable until the pool discards it (by idle timeout or explicit recycle) and a new connection is attempted, at which point the failure occurs.

Confirmed facts

• Entra ID managed identities obtain OAuth access tokens from Microsoft Entra ID with a default lifetime of 1 hour (adjustable via token‑lifetime policies). Azure SDKs refresh tokens before expiry, and Azure SQL Database validates the token on each connection open. If the cached token is expired when the client checks out a pooled connection, the authentication fails immediately.
• SQL logins created with CHECK_EXPIRATION ON enforce password expiration at login time in Azure SQL Database. Once the password’s expiry date is passed, any new login attempt is rejected with error 18487, while existing sessions remain unaffected until they are re‑established.
• Azure SQL Database’s CHECK_EXPIRATION behavior matches on‑premises SQL Server password‑policy timing; the database evaluates pwd_last_set and the default password policy during authentication.

Likely explanation (based on observed behavior)

• Token validation occurs only when a connection is opened. Idle connections in a pool are not re‑validated until they are checked out, so token expiry may manifest as a delayed failure that aligns with the pool’s idle‑timeout or recycle interval rather than the exact token expiry moment.
• Similarly, a connection opened before password expiry remains valid until the pool discards it; the first reuse after expiry triggers the login check and fails immediately at that reuse, not at the exact expiry time.
• Connection‑pool configuration (idle timeout, max connection life, recycle settings) directly influences whether expiration appears as an immediate or delayed failure for both authentication methods.

Steps to verify the behavior for your environment

1. Managed identity test
   1. Enable a system‑assigned managed identity on your Azure SQL Database.
   2. Create a token‑lifetime policy in Microsoft Entra ID (e.g., 10‑minute access token) using Azure AD PowerShell:

      Connect-AzureAD
      New-AzureADPolicy -Definition @('{"TokenLifetimePolicy":{"Version":1,"AccessTokenLifetime":"0:10:0"}}') -DisplayName "ShortLivedToken" -IsOrganizationDefault $false -Type TokenLifetimePolicy
      

   3. Assign the policy to the service principal of the managed identity.
   4. From an application that uses a connection pool (e.g., .NET SqlConnection with Pooling=true), open a connection, wait >10 minutes, then attempt to reuse a pooled connection. Observe failure with error indicating invalid token.

1. SQL login password‑expiration test
   1. Create a SQL login with password expiration enabled:

      CREATE LOGIN [testlogin] WITH PASSWORD = 'Strong!Pass123', CHECK_EXPIRATION = ON;

   2. Set the password to expire immediately (or a known past date):

      ALTER LOGIN [testlogin] WITH MUST_CHANGE, CHECK_EXPIRATION = ON;

   3. Open a connection using this login while the password is still valid, then wait until the expiry moment passes.
   4. Attempt to reuse a pooled connection after the wait. The connection should succeed if the pool still holds the pre‑expiry connection; the next new connection attempt will fail with error 18487.

Missing diagnostic detail that could change the recommendation

To decide whether token‑lifetime management or SQL login password expiration is operationally preferable, you need to know the connection‑pool idle timeout (or max connection life) configured in your client library. If the pool discards connections much faster than the token or password lifetime, expiration will rarely be observed; if the pool holds connections longer, expiration will cause noticeable failures. Please provide your pool’s idle timeout (or confirm that you are using the default).