[Enh]: Enable OBO in DAB (On‑Behalf‑Of delegated identity)

[JerryNixon]

What?

Provide an optional mode where each request is executed with a per‑user Entra ID (Azure AD) access token for https://database.windows.net/.default obtained via OAuth 2.0 On‑Behalf‑Of (OBO) using the inbound bearer token (caller → DAB → Azure SQL).

• Retain current (app / managed identity) mode as default for backwards compatibility.

Problem

Data API Builder (DAB) always connects to Azure SQL using a single application principal (Managed Identity or token supplied at configuration). Per‑user authorization is simulated by pushing claims into SESSION_CONTEXT. Auditing, row‑level ownership enforcement, and least‑privilege scenarios that require the database to recognize the actual caller’s Entra ID (e.g. ORIGINAL_LOGIN()) are not possible. This does not support customers who need true delegated (OBO) identity so that end‑user tokens flow through DAB to the database securely.

Non‑Goals

• Only mssql. No attempt to retrofit OBO for non-Azure SQL engines in first iteration.
• No pooling optimization beyond MVP safeguards (pool likely disabled or segmented).
• No per-user connection reuse beyond simple short-lived caching of access tokens.
• (Intentional strict failure) No fallback to application identity if OBO fails.

Configuration changes

Introduce auth-mode with enum values: application and perUserDelegated.

{
  "data-source": {
    "database-type": "mssql",
    "auth-mode": "perUserDelegated" // allowed: application | perUserDelegated (default: application)
  }
}

• auth-mode = application (default) is the current behavior.
• auth-mode = perUserDelegated (new) is the OBO behavior.

Implementation approach

An OBO helper that is something like this.

dotnet add package Microsoft.Identity.Client

// OboSqlTokenProvider.cs
using System;
using System.Threading.Tasks;
using Microsoft.Identity.Client;
using Microsoft.AspNetCore.Http;
using System.Collections.Concurrent;

public sealed class OboSqlTokenProvider
{
    private static readonly string[] Scope = { "https://database.windows.net/.default" };
    private readonly IConfidentialClientApplication _cca;
    private readonly ConcurrentDictionary<string,(string token, DateTimeOffset exp)> _cache = new();
    private readonly TimeSpan _early = TimeSpan.FromMinutes(5);

    public OboSqlTokenProvider(string tenantId, string clientId, string clientSecret)
    {
        _cca = ConfidentialClientApplicationBuilder
            .Create(clientId)
            .WithClientSecret(clientSecret)
            .WithAuthority($"https://login.microsoftonline.com/{tenantId}")
            .Build();
    }

    public async Task<string> GetAsync(HttpContext ctx)
    {
        string jwt = GetBearer(ctx) ?? throw new InvalidOperationException("No bearer token.");
        string oid = ctx.User.FindFirst("oid")?.Value ?? "no-oid";
        string tid = ctx.User.FindFirst("tid")?.Value ?? "no-tid";
        string key = $"{tid}:{oid}";

        if (_cache.TryGetValue(key, out var entry) && entry.exp - _early > DateTimeOffset.UtcNow)
            return entry.token;

        var result = await _cca.AcquireTokenOnBehalfOf(Scope, new UserAssertion(jwt)).ExecuteAsync();
        _cache[key] = (result.AccessToken, result.ExpiresOn);
        return result.AccessToken;
    }

    private static string? GetBearer(HttpContext ctx)
        => ctx.Request.Headers.Authorization.ToString() is string h && h.StartsWith("Bearer ")
            ? h.Substring(7) : null;
}

Then wire into the MsSqlQueryExecutor sort of like this.

// In constructor DI: inject OboSqlTokenProvider when mode == perUserDelegated
private readonly OboSqlTokenProvider? _obo;

public override async Task SetManagedIdentityAccessTokenIfAnyAsync(DbConnection conn, string dataSourceName)
{
    if (_obo is not null) // perUserDelegated mode
    {
        var http = HttpContextAccessor.HttpContext 
                   ?? throw new InvalidOperationException("No HttpContext.");
        string userToken = await _obo.GetAsync(http);
        var sqlConn = (SqlConnection)conn;

        // Disable pooling (MVP safety)
        var b = new SqlConnectionStringBuilder(sqlConn.ConnectionString) { Pooling = false };
        sqlConn.ConnectionString = b.ConnectionString;

        sqlConn.AccessToken = userToken;
        return;
    }

    // Existing behavior
    await base.SetManagedIdentityAccessTokenIfAnyAsync(conn, dataSourceName);
}