Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dmotter
Frequent Visitor

Setting Session Context

I am building a Power BI dashboard on top of a database that already has row-level security applied through filter and block predicates. These are controlled through session context variables. The app that normally sits on this database calls the sp_set_session_context stored proc to set user id, tenant id, and admin status. I believe I can get the user id (which is unique and internal to our system) through the USERPRINCIPALNAME() email address.

 

The problem I am running into is actually setting the session context in Power BI.
The closest I've gotten is writing a custom query in the "Advanced options" section when creating a SQL Server database data connection. I can put the necessary exec sp_set_session_context lines in, and the preview works, but when it comes time to finalize the query, it complains of a syntax error. When profiling the query, I see that it executes the query inside a subquery, which explains the syntax error, as you cannot execute stored procs in a subquery.

 

I've also tried creating the datasource like normal, and then adding a custom query that just executes a stored proc. My plan was to get that datasource to execute first, and hope the next query uses the same context. This hasn't worked though because it's imperative that I use DirectQuery mode, and from the documentation it seems like stored procs are only supported in Import mode.

 

I've also tried creating a wrapper function for the sp_set_session_context stored proc since it seems like Power BI has better support for functions than procedures. I then add that function as another datasource for the dashboard, but it still doesn't set the session context.

 

Using Power BI's native row-level security is not viable because the database already has row-level security applied. Even the most powerful role in Power BI could not see all the data because any data Power BI gets is already filtered.
What I need is simply a way to set the session context that Power BI runs in.

 

Thanks!

1 ACCEPTED SOLUTION
dmotter
Frequent Visitor

For anyone who has a similar issue, this is how I was able to resolve it:

1) Create a table-valued function in the database. All this function does is call the sp_set_session_context stored proc in such a way as to completely disable row-level security in the database. We have an admin flag, that, if set to true, disables row-level security, so I just call that. However your system is set up may vary, and I was never able to find a way to pass user info to this function. What this function returns isn't really relevant.

2) Create a view for every table that you will want to use in Power BI. The query for that view will just select * from the table and the table-valued function you created in step 1. Like:

SELECT TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

 

This is also a good time to include any new columns allowing you to add multi-column relationships in Power BI. Our database is multi-tenant, so most table have a composite key made of a tenant id and a normal id. Power BI cannot create relationships on multiple columns, so add a new column to the view that is a concatenation of the FK columns. Like:

 

SELECT CONCAT(<col 1>, <col 2>) unique_id, TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

If every table you will use in Power BI has a view like this, you can easily create relationships between tables that have composite foreign keys.

 

3) Add all those views to Power BI as queries and set up the relationships appropriately, making sure to include any tables necessary for row-level security to be calculated by Power BI. In our database, one table controls this, so I included all the tables along the way to reach there.

4) Add a new Users role to Power BI, and set up the USERNAME() function with the instructions here. This allows you to pass in any internal user id that you need, so that USERNAME() will give you something pertinent to your system, and not just an email or domain account or something. Then on the table that control your row-level security, add a new rule for the Users role that filters to just their id. Like "[user_id] = USERNAME()" or something.

View solution in original post

1 REPLY 1
dmotter
Frequent Visitor

For anyone who has a similar issue, this is how I was able to resolve it:

1) Create a table-valued function in the database. All this function does is call the sp_set_session_context stored proc in such a way as to completely disable row-level security in the database. We have an admin flag, that, if set to true, disables row-level security, so I just call that. However your system is set up may vary, and I was never able to find a way to pass user info to this function. What this function returns isn't really relevant.

2) Create a view for every table that you will want to use in Power BI. The query for that view will just select * from the table and the table-valued function you created in step 1. Like:

SELECT TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

 

This is also a good time to include any new columns allowing you to add multi-column relationships in Power BI. Our database is multi-tenant, so most table have a composite key made of a tenant id and a normal id. Power BI cannot create relationships on multiple columns, so add a new column to the view that is a concatenation of the FK columns. Like:

 

SELECT CONCAT(<col 1>, <col 2>) unique_id, TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

If every table you will use in Power BI has a view like this, you can easily create relationships between tables that have composite foreign keys.

 

3) Add all those views to Power BI as queries and set up the relationships appropriately, making sure to include any tables necessary for row-level security to be calculated by Power BI. In our database, one table controls this, so I included all the tables along the way to reach there.

4) Add a new Users role to Power BI, and set up the USERNAME() function with the instructions here. This allows you to pass in any internal user id that you need, so that USERNAME() will give you something pertinent to your system, and not just an email or domain account or something. Then on the table that control your row-level security, add a new rule for the Users role that filters to just their id. Like "[user_id] = USERNAME()" or something.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.