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

v-hashadapu's avatar
v-hashadapu
Icon for Community Support rankCommunity Support
9 months ago

Hi munindra , Thank you for reaching out to the Microsoft Community Forum.

 

Vinodh247  is correct about the core constraint, Fabric SQL Warehouse doesn’t support server-level LOGON triggers or startup procedures, so any automation has to happen on the client side. For applications and services, the right approach is to call EXEC dbo.SetTenantContext immediately after opening the connection, as that’s the only automation possible from the client.

 

There isn’t a reliable or centrally managed run this T-SQL on connect feature in SSMS, especially for MFA/AAD connections to Fabric. You can use templates or startup scripts locally, but that’s not true automation. If you want a completely hands-free setup, remove the session dependency and make the RLS predicate derive the tenant directly from the login, comparing L.TenantID to the row TenantID. This keeps RLS self contained and removes the need to set SESSION_CONTEXT entirely.