PBI Service ignores the dataset’s credentials to the data source.

[dpiret]

Scenario: On-premises SQL Server, with Gateway, having implemented RLS and two users: masterUser (access to all) and userOne (access to a limited set of rows).

In PBI Desktop, I created the following Direct Query datasets,

- datasetMaster using masterUser credentials.

- datasetUserOne using userOne’s credentials.

From PBI Desktop, both work fine: reports based on datasetMaster retrieve all rows; the ones based on datasetUserOne retrieve only the filtered rows.

• Uploaded datasetMaster to Power BI service: works well, all rows retrieved.
• Uploaded datasetUserOne to Power BI service: WRONG retrieves all rows as masterUser, presumably using the same credentials.

*How can I force each dataset to use its credentials*? This already seemed to be a problem back in 2018 [Problem with two dataset of same database but different user access](https://community.powerbi.com/t5/Service/Problem-with-two-dataset-of-same-database-but-different-use...)

The final scenario in case it matters: I need a Power BI single-tenant embedded workspace-based isolation (to allow SaaS users to create their reports) with direct query  (near real-time updates), each workspace accessing only its tenant’s data (RLS on the data source)

Thanks for helping!

[dpiret]

Gotcha, @Greg_Deckler 

Acutally the test was simple: I changed the user in the PBI Service>Gateway>Datasources to UserOne. Now, all reports are filtered, no matter whether the dataset had credentials for Master.

So, it is not a cache issue, but a matter of the Gateway's data source requiring its own authentication, ignoring the dataSet's, for PBI Service only.

I suspect this article Easier set up of datasets in Power BI | Microsoft Power BI Blog | Microsoft Power BI by @kayu  has a key to it, but I struggle to find a stright answer.

thanks!

d

[Greg_Deckler · Accepted solution]

@dpiret Actually, I think you want this:

Multiple data source definitions on the On-premises data gateway for the same data source | Microsof...

[dpiret]

Thanks, @Greg_Deckler that worked like a charm!

I can create data sources programmatically too.

For the sake of completeness, the alternative I found was using query parameters based on Change the Source of Power BI Datasets Dynamically Using Power Query Parameters and How do I pass parameters to my SQL statement?. 

[Greg_Deckler]

@dpiret I thought you said that masterUser should have access to all rows but then you say this is incorrect? Make sure that both users are not Members or Owners of the workspace. RLS does not apply in that situation.

[dpiret]

Hi @Greg_Deckler , thanks for replying!

Let me clarify these points:

• RLS is applied at database level, not Power BI
• Both users (Master and UserOne) are SQL Server users, no email, no relation with PowerBI's or AAD users whatsoever.
• My goal is creating a dataset per workspace that will pull the data from SQL Server (Direct Query) already filtered for each workspace/ DB user. So, tenantOne, will have its SQL Server user, PBI workspace and PBI dataSet. 

The problem

SQL Server user MasterUser works fine in all situations: it retrieves all rows when connecting directly to SQL Server (no Power BI), when I create a dataset with Power BI Desktop and when I upload that dataset to Power BI service.

In the other hand, UserOne works fine in only two situations: it retrieves filtered rows directly on SQL Server, and when I create a dataset in PowerBI Desktop (although I had to clear all credentials from PBI Desktop first, otherwise the previous credentials are cached.)

But, when I upload this dataset to PBI Service (with UserOne's credentials), the data retrieved are not filtered, it retrieves all rows,

My assumption is that either Power BI Service or the Gateway are caching the first credentials, but I have no way to tell.

In case it can help, here's some more information:

- I'm using service principal

- In PBI Service, dataset Settings, Data Source Credentials, I've got "admin has granted access, credentials are not required". This sounds like the problem, but I'm unable to figure out how to work around it.

- The Gateway's Data Source section requires authentication, and is configured using MasterUser's credntial. Maybe the dataset is using these credentials instead of the ones provided in the dataset's configuration?

I understand this architecture should work (SQL Server RLS/SQL Server user/dataset using specific credentials), especially because it is frequently mentioned in MS docs. Example:

Question: My data source already has security roles defined (for example SQL Server roles or SAP BW roles). What is the relationship between these and RLS?

Answer: The answer depends on whether you're importing data or using DirectQuery. If you're importing data into your Power BI dataset, the security roles in your data source aren't used. In this case, you should define RLS to enforce security rules for users who connect in Power BI. If you're using DirectQuery, the security roles in your data source are used. When a user opens a report Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials

 Thanks for helping!

[Greg_Deckler]

@dpiret I believe you are correct in your analysis. Just like how you had to clear the credentials in Desktop to apply the new credentials, the data source is only defined a single time in your Gateway. The easiest way to fix this is to connect to your database using an alternate DNS name. Then this will become a second instance of the data source and you can use the other credentials for it.

[dpiret]

Thanks @Greg_Deckler 

I don't think adding a DNS name per tenant/workspace/dataset will be possible. There are hundreds of them, and should be created along with the customer's SaaS tenant (since it will be used in and embedded "for yourcustomers/App owns data" application.

I'll keep digging, in case there's any other solution 🙂

Thanks!

Thanks!

[Greg_Deckler]

@dpiret I guess my point was to try it and see if it fixed it first. Just to confirm it is in fact the problem you think it is. Because if that isn't the problem then it is something else that we would need to fix. There are other fixes like having multiple gateway clusters, etc and probably a few more that I could come up with but would need to know if it is actually the problem.