Connect to Azure SQL Database from TypeScript with a managed identity

1. The server: Entra only, TLS 1.2, private

// Azure SQL logical server with Microsoft Entra-only authentication and a serverless database
param location string = resourceGroup().location
param serverName string
param adminGroupName string // an Entra group, so admins change without redeploying
param adminGroupObjectId string
param databaseName string = 'appdb'

resource server 'Microsoft.Sql/servers@2025-01-01' = {
  name: serverName
  location: location
  identity: {
    type: 'SystemAssigned' // needs Microsoft Graph read permissions to resolve FROM EXTERNAL PROVIDER users
  }
  properties: {
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Disabled' // reach it through a private endpoint (groupId 'sqlServer')
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: adminGroupName
      sid: adminGroupObjectId
      tenantId: tenant().tenantId
      azureADOnlyAuthentication: true
    }
  }
}

resource db 'Microsoft.Sql/servers/databases@2025-01-01' = {
  parent: server
  name: databaseName
  location: location
  sku: {
    name: 'GP_S_Gen5_2' // General Purpose, serverless, Gen5, max 2 vCores
    tier: 'GeneralPurpose'
  }
  properties: {
    autoPauseDelay: 60 // minutes; -1 disables auto-pause
    minCapacity: json('0.5')
    zoneRedundant: false
  }
}

output fqdn string = server.properties.fullyQualifiedDomainName

Add a private endpoint (groupId: 'sqlServer', zone privatelink.database.windows.net) with patterns/private-endpoint-for-paas.md. Grant the server's system-assigned identity the Microsoft Graph read permissions, or it can't resolve the names in step 2 (reference/sql/connections-and-auth.md).

2. The app's database user

Sign in as a member of the admin group (Azure Data Studio, sqlcmd -G, or the portal query editor) and, in appdb:

CREATE USER [<app identity name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<app identity name>];
ALTER ROLE db_datawriter ADD MEMBER [<app identity name>];

The name is the managed identity's resource name (or the app registration's display name). There is no Azure role that grants data access here: the database user is the permission.

3. Connect and retry

import sql from "mssql";
import { DefaultAzureCredential } from "@azure/identity";

// The identity needs a contained database user: CREATE USER [<identity name>] FROM EXTERNAL PROVIDER;
const pool = new sql.ConnectionPool({
  server: `${process.env.SQL_SERVER}.database.windows.net`,
  database: process.env.SQL_DATABASE ?? "appdb",
  authentication: {
    type: "token-credential",
    options: { credential: new DefaultAzureCredential() }, // tedious requests <server SPN>/.default itself
  },
  // tedious already retries login-time 4060, 10928, 10929, 40197, 40501 and 40613; stretch its 500 ms default
  options: { encrypt: true, maxRetriesOnTransientErrors: 3, connectionRetryInterval: 5_000 },
  pool: { max: 10, idleTimeoutMillis: 30_000 },
  connectionTimeout: 30_000,
});

const TRANSIENT = new Set([40613, 40501, 40197, 40540, 40143, 10928, 10929, 10053, 10054, 10060, 233, 64]);

// Query errors carry `number`; login errors arrive as code "ELOGIN" with only the server's message.
function isTransient(e: unknown): boolean {
  const err = e as { number?: number; code?: string; message?: string };
  if (err.number !== undefined) return TRANSIENT.has(err.number);
  return ["ETIMEOUT", "ESOCKET"].includes(err.code ?? "") ||
    (err.code === "ELOGIN" && /is not currently available|currently busy/.test(err.message ?? ""));
}

export async function withRetry<T>(work: (p: sql.ConnectionPool) => Promise<T>): Promise<T> {
  for (let attempt = 0, delay = 5_000; ; attempt++, delay = Math.min(delay * 2, 60_000)) {
    try {
      if (!pool.connected) await pool.connect();
      return await work(pool);
    } catch (e) {
      if (attempt >= 5 || !isTransient(e)) throw e;
      await new Promise((r) => setTimeout(r, delay)); // 5 s first, doubling to 60 s
    }
  }
}

export async function main(): Promise<void> {
  const rows = await withRetry((p) =>
    p.request().input("id", sql.Int, 42).query<{ name: string }>("SELECT name FROM dbo.customers WHERE id = @id"),
  );
  console.log(rows.recordset[0]?.name);
  await pool.close();
}

• type: "token-credential" lets tedious get a token for the server's own SPN (https://database.windows.net/.default) and refresh it for new connections. Use ManagedIdentityCredential with a client ID when the app has several identities.
• Retries: tedious already retries 4060, 10928, 10929, 40197, 40501 and 40613 during login, but only 500 ms apart by default, so the example stretches connectionRetryInterval to Microsoft's 5 seconds and wraps calls in an exponential backoff to 60 seconds. Login errors come back with code: "ELOGIN" and no number (the wrapper matches the message); query errors carry number. The hub tested the wrapper's types, not a live failover.
• Don't retry a failed write blindly: retry only work that's idempotent or inside a transaction you know was rolled back.

4. Check it

Next

Error meanings and fixes: reference/sql/errors.md. Other databases with the same identity pattern: patterns/app-to-database-with-managed-identity.md.