[Feature Request] Support RLS via SESSION_CONTEXT set per-request server-side

[lbertrand]

Summary

Tables protected by SQL Server Row-Level Security commonly evaluate their predicate against SESSION_CONTEXT() rather than the login, because the app connects through one shared service account and passes tenant/user scope per request. Those tables are currently unqueryable through this server: the session context is never set, so RLS filters everything out and every query returns an empty recordset — with no error to indicate why.

Current behaviour

QueryValidator blocks the only way to set it. EXEC sys.sp_set_session_context @key=N'TenantId', @value=N'42' trips three separate rules in FORBIDDEN_KEYWORDS (src/security.ts:10) — EXEC, EXECUTE, and SP_ — and also fails the ALLOWED_STATEMENTS prefix check. There is no config surface for it either: ConnectionConfigSchema (src/types.ts:3) has no init-SQL or session-context field.

The pooling constraint

This is the part that determines the design. SqlServerConnection holds a pool of up to 10 (src/connection.ts:27) and query() calls this.pool.request() per statement (src/connection.ts:46). SESSION_CONTEXT is connection-scoped, so:

• A separate set_session_context MCP tool would set the context on whichever connection the pool happened to hand out, and the following execute_query call would very likely get a different one. It would appear to work intermittently, which is worse than failing outright.
• Whitelisting EXEC sp_set_session_context in the validator has the same problem, unless the caller sends the EXEC and the SELECT in one batch — in which case the whitelist has to reason about multi-statement batches, exactly what the validator is designed to reject.
• Connection-time initialization doesn't fit either. mssql does expose a beforeConnect hook (lib/tedious/connection-pool.js:113), and it does fire per pooled connection — but it receives the raw tedious connection before it is established, so there is no reliable point to run initialization SQL and have it complete before the pool hands the connection out.

Proposal: set the context per request, in the same batch

Build the preamble inside SqlServerConnection.query(), from configuration the model never touches:

const request = this.pool.request();
const preamble = this.applySessionContext(request);
return await request.query(preamble + queryText);

which emits, for SQLSERVER_SESSION_CONTEXT='{"TenantId":42,"UserRole":"auditor"}':

IF SESSION_CONTEXT(@__sc_key_0) IS NULL EXEC sys.sp_set_session_context @key = @__sc_key_0, @value = @__sc_val_0, @read_only = 1;
IF SESSION_CONTEXT(@__sc_key_1) IS NULL EXEC sys.sp_set_session_context @key = @__sc_key_1, @value = @__sc_val_1, @read_only = 1;
SELECT TOP 1000 * FROM dbo.Invoices

Why this shape:

• Same batch ⇒ same connection, so it is correct under pooling by construction.
• security.ts needs no changes. The EXEC is generated by the server from config, never parsed out of model-supplied input, so the read-only guarantee on the query parameter is untouched and the threat model doesn't widen. The preamble is prepended after addRowLimit(), whose /^(\s*SELECT\s+)/i anchor would otherwise stop matching.
• Injection-proof by binding, not escaping — keys and values go through request.input(). Numbers bind as int so RLS predicates comparing against integer columns work without a CONVERT.
• No behaviour change when unconfigured — the preamble is empty and the emitted SQL is byte-identical to today's.

@read_only = 1 by default, with an explicit reset

Read-only keys are worth having here: they mean a query cannot widen its own scope even if it reached the server with the context already established — useful defence-in-depth given that WITH 'x' AS (SELECT 1) ... already slips past the prefix check (noted in #1).

The obstacle is that pooled connections are reused, and re-running the EXEC against an already-locked key raises error 15664. Guarding each EXEC on SESSION_CONTEXT(@key) IS NULL makes it idempotent: the first query on a given connection sets the key, later ones skip it. Since the context comes from process-level config, every connection converges on the same values.

That leaves changing a value at runtime, which read-only genuinely blocks. Read-only keys are released only on connection reset — tedious can reset one connection (Connection.reset), but mssql never calls it and exposes no per-checkout hook, so the supported route is recycling the pool. Hence resetSessionContext(), which does exactly that. SQLSERVER_SESSION_CONTEXT_READONLY=false opts out and allows in-place overwrites.

---

Implementation in the linked PR: +159 lines, no changes to security.ts, typechecks clean. Happy to adjust the config surface if you'd prefer a different shape.