Forum Discussion

munindra's avatar
munindra
Frequent Visitor
9 months ago
Solved

How to Dynamically Set Tenant Context in Fabric SQL Warehouse with MFA Login and RLS

I’m implementing Row-Level Security (RLS) in Microsoft Fabric SQL Warehouse for a multi-tenant architecture. The goal is to filter data based on TenantID using SESSION_CONTEXT and a security policy a...
data engineering
lakehouse
  • munindra's avatar
    munindra
    9 months ago

    By passing the SESSION_CONTEXT directly into the function rather than creating a seperate logic i am able to resolve this issue

Vinodh247's avatar
Vinodh247
Super User
9 months ago

  1. SSMS startup script:
    Users can configure SetTenantContext to run automatically via
    SSMS -> Options -> Query Execution -> SQL Server -> General -> “Execute the following script on connection”.
    --> This is the simplest method for MFA users.

  2. Custom connection wrapper:
    If connecting through an app or service (PBI, ADF, etc.), call EXEC dbo.SetTenantContext immediately after connection opens.

  3. Fabric alternative:
    Automate tenant context logic within the query layer by joining tenant-aware views (instead of relying on session context).

the only viable automation for SSMS mfa users today is via SSMS startup script.