[Enh]: Session Context Claim Forwarding

[JerryNixon]

See also: #3078

Config (Data Source Options Section)

{
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('SQL_CONN')",
    "options": {
      "set-session-context": true,
      "session-context-claims": [ // new
        "sub",
        "preferred_username",
        "realm_access.roles",
        "https://example.com/tenant"
      ]
    }
  }
}

What?

Add session-context-claims under data-source.options to control exactly which JWT claims are forwarded to SQL Server's sp_set_session_context.

Why?

Today, when set-session-context is true, DAB forwards all claims from the authenticated identity. Users have no control over which claims are sent or how nested and namespaced claims are resolved.

A simpler model is to resolve the configured claim path and store its value in SESSION_CONTEXT as text. Scalar values are stored as text. Arrays and objects are stored as serialized JSON text. SQL can then use OPENJSON and other JSON functions when needed.

Samples

Simple string claim

// JWT Payload
{ "sub": "abc123" }

// DAB Config
"session-context-claims": [
  "sub"
]

-- SESSION_CONTEXT
sub → "abc123"

Named string claim

// JWT Payload
{ "preferred_username": "bob" }

// DAB Config
"session-context-claims": [
  "preferred_username"
]

-- SESSION_CONTEXT
preferred_username → "bob"

Flat array claim

// JWT Payload
{ "roles": ["admin","writer"] }

// DAB Config
"session-context-claims": [
  "roles"
]

-- SESSION_CONTEXT (stored as JSON text)
roles → '["admin","writer"]'

Nested array claim

// JWT Payload
{ "realm_access": { "roles": ["editor"] } }

// DAB Config
"session-context-claims": [
  "realm_access.roles"
]

-- SESSION_CONTEXT (stored as JSON text)
realm_access.roles → '["editor"]'

Namespaced string claim

// JWT Payload
{ "https://example.com/tenant": "contoso" }

// DAB Config
"session-context-claims": [
  "https://example.com/tenant"
]

-- SESSION_CONTEXT
https://example.com/tenant → "contoso"

Namespaced array claim

// JWT Payload
{ "https://example.com/groups": ["A","B"] }

// DAB Config
"session-context-claims": [
  "https://example.com/groups"
]

-- SESSION_CONTEXT (stored as JSON text)
https://example.com/groups → '["A","B"]'

Object claim

// JWT Payload
{ "profile": { "name": "Bob", "region": "west" } }

// DAB Config
"session-context-claims": [
  "profile"
]

-- SESSION_CONTEXT (stored as JSON text)
profile → '{"name":"Bob","region":"west"}'

Spec: session-context-claims

• Type: string[]

• Default: null which means when set-session-context is true, preserve current behavior and forward all flat claims.

• Entry format: <claim-path>

  • <claim-path> follows the same resolution rules as rolesPath: URL prefix means literal key, dot means nested traversal, otherwise literal key.

Behavior:

• Resolve the configured claim from the JWT.
• If the value is a string, number, or boolean, store its text value in SESSION_CONTEXT.
• If the value is an array or object, store its serialized JSON text in SESSION_CONTEXT.
• If the value is null, skip and log at Debug.
• SQL can parse stored JSON text using functions such as OPENJSON.

Notes:

• SESSION_CONTEXT stores scalar SQL values only. Arrays and objects are persisted as JSON text.
• Missing claim: Skip silently and do not fail the request. Log at Debug level: "session-context-claims: claim '{path}' not found in JWT, skipping."
• SESSION_CONTEXT key: Use the full <claim-path> string as the key name in sp_set_session_context.

Backward Compatibility

set-session-context	session-context-claims	Behavior
false	any	No claims forwarded
true	null / omitted	Forward all flat claims
true	[] (empty array)	Forward nothing
true	["sub", ...]	Forward only listed claims

Validation

Case	session-context-claims	set-session-context	database-type	Result
1	set	false	any	Startup error ("session-context-claims requires set-session-context to be true.")
2	null, empty, or whitespace entry	true	MSSQL	Startup error
3	set	true	not MSSQL	Startup warning ("session-context-claims is only supported for MSSQL.") and skip at runtime
4	null / omitted	true	MSSQL	OK (forward all flat claims)
5	empty array []	true	MSSQL	OK (forward nothing)
6	valid entries	true	MSSQL	OK (forward only listed claims)
7	claim not found in JWT	true	MSSQL	Skip and log at Debug
8	resolved value is string, number, or boolean	true	MSSQL	Store text value
9	resolved value is array	true	MSSQL	Store serialized JSON text
10	resolved value is object	true	MSSQL	Store serialized JSON text
11	resolved value is null	true	MSSQL	Skip and log at Debug

Implementation

Session Context Flow

SQL query execution
  → GetSessionParamsQuery called (existing)
  → If session-context-claims is null:
    → Forward all flat claims (existing behavior)
  → If session-context-claims is non-null:
    → For each entry "path":
      → Resolve claim value (literal key / dot-path / URL key)
      → If missing: skip, log debug
      → If string, number, or boolean: convert to text and set sp_set_session_context 'path', @value
      → If array or object: serialize as JSON text and set sp_set_session_context 'path', @jsonValue
      → If null: skip and log debug