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

[munindra]

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 and access trough SSMS
What I’ve Done:

Created an inline TVF and security policy:

Created a mapping table:

Created a stored procedure to set tenant context dynamically:

Current Behavior:

• After MFA login in SSMS, I run:
  
  • RLS works perfectly after that.

  Question: Is there a way to automate this step (running the stored procedure) so users don’t have to execute it manually every time they log in?
  I know Fabric doesn’t support server-level triggers. Can this be done via SSMS startup scripts, Data Activator, or any other Fabric feature?

[v-hashadapu]

Hi @munindra , 

hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[munindra · Accepted solution]

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

[v-hashadapu]

Hi @munindra , Thanks for the update and the information. We are happy to know that you have resolved your issue. If you have any other queries/issues, feel free to create new posts, we are always happy to help.

[v-hashadapu]

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.

[Vinodh247]

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.