Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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).
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.
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).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
32 | |
26 | |
23 | |
19 | |
15 |
User | Count |
---|---|
51 | |
38 | |
24 | |
20 | |
20 |