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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MichielE
New Member

Dynamic Row Level security from datasource and not by Power BI

Dear all,

 

I need help with a rather "advanced" scenario, of which I think is currently not fully possible with the current Power BI ecosystem.

 

What scenario we're trying to achieve?

We have a big enterprise worldwide organization (NGO), which wants Power BI to function as the organization wide Reporting/BI platform. We have multiple API's running (.NET Rest API's) with Azure SQL as backend.

We want to provide generaly available datasets and reports/dashboard to the users, where the users only see the data they are allowed to see (rows and columns). The data should come from these API's and/or databases. Users should not, by accident, see data they wheren't allowed to see (due to caching or due to imported data).

 

Currently the security and access managed is arranged in the API's/SQL. We don't to do this again on Power BI level, since this casues duplicate administration and access filtering is a rather complex setup, which we can deal with on SQL server level, but less so on Power BI level. Power BI RLS is not giving us the enough cababilities to deal well with the column level security.

 

The solution we hoped for (and directly a feature request 😉 )

The idea was trying to solve this with Direct Query and connecting that with the Rest API (through OData or just JSON or other data formats). However the current connectors don't support Direct Query when connecting with a Web resource.

The hope was that with Direct Query, the organizational account whould pass through to the API (of the user opening the report), the API passes it through to SQL Server and uses SQL Row Level Security to filter it's data. 

 

Since this doesn't currently work, what scenario can we make work currently with Power BI?

 

Alternative options I can think of

1. Connect Power BI directly with Azure SQL (I don't like this from an architectural perspective, also not from a security perspective (exposing the SQL database fully to Power BI).

2. Creating a custom connector with Direct Query support using Organizational account passthrough. Needs to work on the Power BI cloud environment. Not sure if this fully works in this scenario, since you need to go through an on-premise gateway and if this works with the authentication passthrough.

3. Emulating a protocol on the API it's side for which a connector exists with Direct Query. Not sure if that's fully feasible. TDS comes to mind (like MS does with dataverse). Yet there are probably no TDS server implementations already available in .NET (only clients).

 

The current Architecture

 

MichielE_0-1665218133551.png

This is the current architecture. The API currently uses MSAL with OAuth authentication with Azure AD. Where currently I'm able to connect to from Power BI cloud with my organizational account (Oauth works fine), without custom connector.

 

Does anyone have a idea on how we can achieve our desired situation?

 


By the way: I do understand performance issues with Direct Query and it goes directly against working with an internal OLAP cube (pre-calculated fields).

 

 

 

2 REPLIES 2
MichielE
New Member

Hi @GilbertQ, yes it's quiet a requirement, which might comes accross as over design. However we're trying to look at a long term system design for this, instead of a short term fix for the actual situation we're trying to solve at this stage.

 

If Power BI allowed for Direct Query directly to the API with passing through the SSO, that would have solved this issue completely. Otherwise we might need to be the OLAP cube at the API side.

GilbertQ
Super User
Super User

Hi @MichielE 

 

That is quite a requirement. One thing I would say is that currently in PBI you cannot secure both rows using RLS and columns using Object Level Security at the same time. It is a known limitation.

 

For what you are trying to do it is quite hard to get done and I am not 100% sure that you can pass through SSO via an API (I think it can be done via Azure SQL).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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