Connect Azure SQL with Managed Identity

No passwords in code

The DS platform connects to Azure SQL using Managed Identity. There is no SQL username. There is no SQL password. There is no connection string with credentials baked in. The application proves its identity to the database using an Azure Active Directory (AAD) token that the Azure runtime provides automatically.

This matters for state government because:

• No secrets to rotate — there is no password that expires or gets committed to a repo
• No credentials in environment variables — the token is obtained at runtime from the Azure Instance Metadata Service (IMDS)
• Audit trail — AAD logs every token issuance, so you can prove which app accessed which database and when
• CJIS compliance — Criminal Justice Information Services requires that database credentials are not stored in application code. Managed Identity satisfies this without the overhead of a vault fetch on every request.

How token auth works

The connection flow has four steps:

1. App Service starts — Azure assigns a Managed Identity to the App Service instance. This identity is an AAD service principal with no password.
2. App requests a token — When getDbPool() initializes, it calls the Azure IMDS endpoint to get an AAD access token scoped to Azure SQL (https://database.windows.net/.default).
3. Token goes to mssql driver — The tedious driver (used by the mssql package) accepts the token in the connection config instead of a username/password.
4. Azure SQL validates the token — The database checks that the token’s service principal has been granted access (via CREATE USER [app-name] FROM EXTERNAL PROVIDER).

In Azure Gov, the IMDS endpoint and the token audience are different from Azure commercial. The DS platform’s getDbPool() handles this, but AI-generated code often uses commercial endpoints. That is the first thing to verify.

Why edge middleware cannot access the database

Next.js middleware runs in the Edge Runtime. The Edge Runtime is a V8 isolate — not a full Node.js process. It does not have access to:

• The net module (required by the tedious TCP driver)
• The tls module (required for encrypted SQL connections)
• The crypto module’s full API (required for token signing)

The mssql package depends on tedious, which depends on all three. Importing mssql in middleware causes a build error:

Module not found: Can't resolve 'net' in edge runtime

This is not a bug. It is an architectural constraint of the Edge Runtime. The platform rule is clear: database access happens only in API route handlers (which run in the Node.js runtime), never in middleware, never in client components, and not in React Server Components at this time.

The connection utility

The getDbPool() function manages a singleton connection pool. Here is what it does:

1. Creates a single mssql.ConnectionPool on first call
2. Acquires an AAD token from Managed Identity (or falls back to DefaultAzureCredential for local dev)
3. Configures the tedious driver with the token
4. Enables connection pooling (max 10 connections, idle timeout 30s)
5. Returns the pool for reuse across concurrent requests

The AI can generate this utility — but it needs specific instructions about the Azure Gov token endpoint and the local development fallback.

The prompt

Create the database connection utility at src/lib/db.ts for the DS platform.

Requirements:

1. MANAGED IDENTITY AUTH (production):
   - Use @azure/identity DefaultAzureCredential to get an AAD token
   - Token scope for Azure Gov: 'https://database.usgovcloudapi.net/.default'
   - Pass the token to the mssql ConnectionPool config via authentication.token
   - The credential must set authorityHost to
     'https://login.microsoftonline.us' (Azure Gov, not commercial)

2. LOCAL DEV FALLBACK:
   - If AZURE_SQL_USER and AZURE_SQL_PASSWORD env vars are set, use SQL auth
   - This is for local development only -- production never uses SQL auth
   - Add a console.warn when falling back to SQL auth so it's visible in logs

3. CONNECTION POOL:
   - Singleton pattern: create the pool once, reuse across requests
   - Pool config: max 10, min 0, idleTimeoutMillis 30000
   - Server name from AZURE_SQL_SERVER env var
   - Database name from AZURE_SQL_DATABASE env var
   - Encrypt: true (required for Azure SQL)
   - trustServerCertificate: false in production, true in local dev

4. ERROR HANDLING:
   - If pool connection fails, log the error (without credentials) and throw
   - Export a getDbPool() function that returns the connected pool
   - Export a closePool() function for graceful shutdown

5. TYPES:
   - Import sql from 'mssql'
   - Import { DefaultAzureCredential } from '@azure/identity'
   - Full TypeScript with proper types, no 'any'

IMPORTANT:
- This file runs in Node.js runtime ONLY -- never import it in middleware
  or client components
- Use 'mssql' package (not 'tedious' directly)
- The token must be refreshed before expiry -- DefaultAzureCredential
  handles this automatically
- Do NOT hardcode any server names, database names, or credentials

Watch it work

What to verify in the generated code

Local development setup

For local development, you cannot use Managed Identity (your laptop is not an Azure App Service). The platform uses a SQL auth fallback:

# .env.local (git-ignored)
AZURE_SQL_SERVER=your-dev-server.database.usgovcloudapi.net
AZURE_SQL_DATABASE=ds-platform-dev
AZURE_SQL_USER=dev-user
AZURE_SQL_PASSWORD=dev-password

The getDbPool() function detects these env vars and falls back to SQL auth with a console warning. This is acceptable for local dev. It is never acceptable for staging or production — those environments use Managed Identity exclusively.

The architecture boundary

Here is the clear rule for where database access can happen in the DS platform:

This boundary is a team decision, and it is enforced by convention plus the build system. If someone imports @/lib/db in middleware, the Next.js build will fail with a module resolution error. If someone imports it in a client component, the mssql driver crashes at runtime.

What’s next

Your API routes talk to Azure SQL through Managed Identity. Now the frontend needs to consume that data. The next lesson covers SWR data flows — shared fetchers, mutation feedback with MUI Snackbar, and why empty catch blocks are banned on this platform.