Row Level Security in Power BI Paginated not working

[george_p]

Hi everyone,

I have some paginated reports that are using SSAS server as a data source, however, my Row-level security set up in the server is not working on my Paginated reports. My users see everything and it seems like the queries being sent to the server are generated by the service account used by Power BI Gateway. Can someone help me with that?

Thanks,
Georgi

[v-yetao1-msft]

Hi @george_p 

When you connect SSAS with Live Connetion mode , you can manage RLS in SSAS . Add users in SSAS, then assign different roles to these users .Finally, the user's RLS management is realized by combining with the users in Power BI Service by means of map users.

You can refer to the link below to learn how to map users in gateway .

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-ssas#manual... 

You also mentioned you have a large number of users to add , adding one by one is a lot of work .  You can create a group and then bulk add users to this group you created , then you can match groups to SSAS users just like users. 

Create groups can refer to the link below .

https://docs.microsoft.com/en-us/microsoft-365/admin/create-groups/create-groups?view=o365-worldwide 

To add users to groups in batches, you can refer to the following link.

https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/groups-bulk-import-members 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mroldx]

Unlike sql, which can use where plus conditions, how should the MDX statement used by AzureAS be used with the @UserID parameter

[george_p]

Hi,

Thanks for the answer. I already have RLS setup in my SSAS instance with an AD security group of users for my role ( there are thousands of users in that group). My question is how to set up the mapping rule of the users in the Gateway by using that group. From the article, you sent me it doesn't get clear what should I enter in the "Replace" and "With". I tried with a specific user by placing the login email in the "Replace" field and the windows login in the "With" field but it still doesn't work.

I tried running a trace with SQL Profiler I am noticing that for Normal Power BI reports an EffectiveUserName is getting passed in the query property list, whereas for paginated reports there is no such property.

[GilbertQ]

Hi @george_p 

I could not see anything in the limitations here: What are paginated reports in Power BI Premium? - Power BI | Microsoft Docs

The only thing I could think of is it is not passing through the details correctly to your SSAS Instance? Have you mapped the usernames?

[george_p · Accepted solution]

Hi @GilbertQ 

I spoke to MS Support and they told me that currently, this is not possible. They quoted this article (https://docs.microsoft.com/en-us/power-bi/developer/embedded/paginated-reports-row-level-security#us...). Really weird location for a paginated reports limitation, don't you think 🙂

The product team is currently working on enabling this and it should be coming in the next semester ( 6 months ) but they don't know when exactly.

For now, I am passing the UserID system variable to my queries and applying the RLS logic in the DAX queries. It's doing to job for me while I wait for a solution.

Thanks for the assistance though!

Regards,

Georgi

[GilbertQ]

Thanks for letting me know, I was not aware of this limitation!

[george_p]

@GilbertQ, thanks but I have thousands of users, and managing it from here would be a living hell, so I would need to find another solution. Is anything else coming up to your mind?

[GilbertQ]

Hi @george_p 

The only other way is to make some changes on the Gateway, but in order to do that you would also have to make changes to the Active Directory user accounts to add in their email addresses to an un-used property.

Unfortunately there is no easy work around. 

The only other option is to use AAS or Power BI Premium or Power BI Premium Per user.

[GilbertQ]

Hi @george_p 

What you need to do is to map the UPN (users email address) through to your SSAS Server so that it passes through the users credentials.

You can do this in the Gateway, data source settings and then map as shown below.