[OBO] JWT Validation, Identity Extraction, Cache‑Key Construction, SQL Token Acquisition & Delegated SQL Execution (No Pooling)

[anushakolan]

• Validate inbound JWT:

1. signature
2. issuer
3. audience (must match DAB)
4. expiry

• Extract identity claims:

1. use oid when present
2. fallback to sub when oid is missing (guest/B2B)
3. extract tid

• Reject request if neither oid nor sub exists → 401

• Compute authorization‑context hash from permission‑affecting claims

• Construct cache key exactly as defined:
  (oid OR sub) + tid + authorization‑context hash

• Enforce design‑mandated validation rules:

1. database-type must be mssql
2. database-audience required
3. token-cache-duration-minutes set a defaulkt value of 10 within the code.

Acceptance Criteria / Tests

• missing oid+sub → 401
• invalid config fails deterministically

=======================================================`

Implement delegated SQL execution using OAuth2 On‑Behalf‑Of with in‑memory token caching and non‑pooled SQL connections.

• Acquire SQL access token using AcquireTokenOnBehalfOf

  1. inbound user JWT as assertion
  2. scope derived by appending /.default to database-audience

• Cache SQL tokens in memory only, keyed by cache key from Issue 1

• Perform early refresh before expiry

• Do not refresh tokens during an active SQL query

• Open SQL connections with:

  1. SqlConnection.AccessToken = delegated SQL token
  2. pooling disabled

• Error handling:

  1. OBO failure → 401
  2. no fallback to app identity

• Acceptance Criteria / Testss

  1. audience correctly converted to /.default
  2. token reused from cache when valid
  3. refresh happens only before query
  4. no refresh during active query
  5. OBO failure always returns 401
  6. SQL executes as delegated user