Session Metadata / Auditing API in mssql-python - API Spec & Discussion

[kapilsamant]

Issue #621

Session Metadata / Auditing API Specification

Overview

The Connection.set_audit_context() / Connection.get_audit_context() methods provide session-level auditing and tracing metadata on SQL Server connections. Values are stored via sp_set_session_context and are visible to SESSION_CONTEXT() in T-SQL, Extended Events, sys.dm_exec_sessions, and audit specifications.

Is your feature request related to a problem?

There is no built-in way to attach session-level auditing or tracing metadata to a Connection in mssql-python. Applications that need to propagate end-user identity, module names, or action context to SQL Server for auditing or Extended Events tracing must manually execute raw sp_set_session_context calls, handle input validation themselves, and track which keys have been set. This is error-prone, verbose, and inconsistent across codebases.

Applications that need to comply with regulations (GDPR, PCI-DSS, etc.) must propagate end-user identity, module context, and action tracking to SQL Server for audit trail generation and compliance reporting.

API Specification

Connection.set_audit_context()

Connection.set_audit_context(
    *,
    application: Optional[str] = None,
    module: Optional[str] = None,
    action: Optional[str] = None,
    user_id: Optional[str] = None,
    read_only: bool = False,
    **extra: str,
) -> None

Connection.get_audit_context()

Connection.get_audit_context() -> Dict[str, str]

Returns a copy of the locally cached session context dictionary. Does not round-trip to the server.

Parameters Reference

Well-Known Parameters

Parameter	Session Context Key	Description
application	application_name	Logical application name
module	module_name	Module or component name
action	action_name	Current action or operation
user_id	user_id	End-user identifier

Behavior Parameters

Parameter	Type	Default	Description
read_only	bool	False	If True, keys become immutable for the remainder of the SQL Server session
**extra	str	—	Arbitrary additional key-value pairs stored in the session context

Validation Rules

Constraint	Limit	Rationale
Key type	Non-empty str	sp_set_session_context requires sysname keys
Key length	128 characters	SQL Server sysname limit
Value type	str only	Enforced before execution; rejects non-string with ProgrammingError
Value length	4,000 characters	sql_variant has an 8,000-byte limit; driver binds strings as NVARCHAR (2 bytes/char), so 4,000 chars = 8,000 bytes

Exception Types

Exception	Condition
ProgrammingError	Invalid key/value (wrong type, exceeds length, empty key)
InterfaceError	Connection is closed
DatabaseError	sp_set_session_context execution failure (e.g., writing to a read_only key)

Behavior

• Merge semantics: Successive calls merge new values with previously set ones.
• Clearing a key: Pass "" (empty string) — the driver sends NULL to sp_set_session_context and removes the key from the local cache.
• read_only=True: Makes the keys immutable for the remainder of the SQL Server session. Subsequent attempts to change them will raise DatabaseError from the server.
• Parameterized execution: All sp_set_session_context calls use parameterized queries to prevent SQL injection.
• Local cache: get_audit_context() returns a copy of the internal dict to prevent accidental mutation.

Usage Example

import mssql_python

conn = mssql_python.connect("Server=localhost;Database=mydb;Trusted_Connection=yes")

# Set auditing metadata — all options visible in IDE autocomplete
conn.set_audit_context(
    application="BillingAPI",
    module="InvoiceProcessor",
    action="GenerateInvoice",
    user_id="user-42",
)

# Add custom keys
conn.set_audit_context(tenant_id="ACME", correlation_id="abc-123-def")

# Lock a key for the session
conn.set_audit_context(action="FinalAction", read_only=True)

# Read back (local cache, no server round-trip)
ctx = conn.get_audit_context()
# {'application_name': 'BillingAPI', 'module_name': 'InvoiceProcessor',
#  'action_name': 'FinalAction', 'user_id': 'user-42',
#  'tenant_id': 'ACME', 'correlation_id': 'abc-123-def'}

Values are now readable in T-SQL:

SELECT SESSION_CONTEXT(N'application_name')  -- 'BillingAPI'
SELECT SESSION_CONTEXT(N'user_id')            -- 'user-42'
SELECT SESSION_CONTEXT(N'tenant_id')          -- 'ACME'

Visibility

Values set through this API are visible to:

SQL Server Feature	Visibility
SESSION_CONTEXT(N'<key>')	T-SQL queries, triggers, stored procedures
Extended Events	Sessions that capture session context
sys.dm_exec_sessions	For application_name
Audit specifications	Specifications that reference session context

Alternatives Considered

1. Raw cursor execution — cursor.execute("EXEC sp_set_session_context @key=?, @value=?", ...). Works but requires boilerplate, duplicates validation logic, and lacks discoverability.

2. Connection string properties — Encoding audit metadata into the connection string (e.g., Application Name). Only covers a single field, is immutable after connect, and doesn't flow to SESSION_CONTEXT().

[saurabh500]

@kapilsamant First of all, I think this is a very useful feature.

Some recommendations/notes/questions/comments.

1. Can you define the impact of this API on pooled connection ?

• What if the attributes are set, the connection returns to the pool on close, and then is retrieved from the pool.

2. Naming: Can we pick set_session_context / get_session_context ? (matches the T-SQL function and specifies a clear mapping between the intention and the internals)

3. Hidden intention of empty string

Clearing a key: Pass "" (empty string) — the driver sends NULL to sp_set_session_context and removes the key from the local cache.

Can we use None instead of using an empty string, or why not expose clear_session_context(*keys) ? The intention is lot clear with None. The default Value is anyhow None, which means that nothing is set. Clearing the values can be made symmetric as well.

4. There is mismatch of the names of property on the Python API and the keys in the session context. Can we make them match to reduce cognitive overload and debugging overload. e.g. application in python to application_name in session context can be simply application or application_name in both places.

5. It looks like the intention is to send multiple sp_set_session_context PROCS to T-SQL and hence N keys will have N round trip calls. Can we consider sending all the Keys in a single call ?

6. The getter intends to get the items from the cached value. This is great as long as T-SQL sent to the server itself doesn't mutate the session keys. But if the API is used to set the session properties, a T-SQL in the session modifies the property, and then the API is used to get the Session property, then the cached value and the real value on server would be out of sync. Does it make sense to retrieve these values from the server instead ? Or do we always want to show the driver cached value? I am not sure about the usage patterns, so it would be good to clarify this behavior.

[kapilsamant]

@saurabh500 Thanks for the review

1 and 3 are good catches, I missed those completely. While 3 is an easy fix. For 1, I'll need to think through how session context cleanup should work when a connection is returned to the pool.

2 ,4 and 5 - agreed on all three, will update.

For 6, the intention behind the cache is to know which keys were set in the session, because there's no way to enumerate all session context keys from the server. You have to SELECT SESSION_CONTEXT() for each key individually, which means you need to know the key names upfront. I agree on the staleness risk though. I think the right approach is -> store the key names in the dict so we know what to query, and have get_session_context() use SELECT SESSION_CONTEXT() to fetch the latest value for each known key every time it's called.