Permissions based on SSAS cubes

[Maieev]

Hi,

I have a complicated problem. I have a report based on two OLAP cubes (SSAS) that are shared within organisation to work with. Security is on the cube level - so to see data from cube users have assigned access on country level.

To build this report based on two cubes and combine data from them I needed to change model to mixed, some data is direct query some is import because I created calculated tables, hence to have current data in the published report I need to refresh it in desktop app and publish again.

It turned out that users who should see data only for some countries - can see all data - no permission was inherited from cube level security.

Could you please help me understand where exactly is the mistake and how to fix it?

The cubes are extremely heavy, I don't work with SQL, so don't know if I can connect only to some of the data, also to make relationships between them and make the data work was extremely hard, so I'm very unhappy that this happened and have no clue how to fix it :(.

[GilbertQ · Accepted solution]

Hi @Maieev 

What you can do is to use the user mapping feature to your on premise analysis services cube.

Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn

[v-jtian-msft · Accepted solution]

Hi,@Maieev.Hello, @GilbertQ ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below

According to your description, the report you built uses the AS Multidimensional Model as the data source, and the model itself still has RLS set up.

But the RLS is not working on the power BI service.

Here is my analysis, you can follow the steps I provided to troubleshoot the problem.

1. For a report whose data source is an Analysis service model, it is not possible to set up RLS in power BI desktop directly, because RLS targets the dataset itself.

Therefore, for Analysis service models (multidimensional models/tabular models), you need to set up RLS for the model itself.

you need to set up RLS for the model itself

So you need to check if the RLS setup in the Analysis Services model itself is working.

Here is my test:

You need to check that the permissions of your configured roles are limited to Read/Process only, and that no Admin permissions can be assigned (that would invalidate RLS/OLS).

After checking the data model itself, I recommend that you check the deployed AS servers to make sure that the relevant RLS settings are correct.
like this :

Please check that your DAX code is working correctly.
Very important: you need to make sure that you still have the required access users added.

If you make sure that all the above configurations are OK, it means that the problem may be on the power bi service: the gateway connection configuration may be wrong.

2. Check the configuration of the power bi service.

You need to ensure that the map user names are configured correctly

Simply put, this mapping mechanism ensures that the user account currently accessing reports on services (the power bi service account) is mapped to the account that has access to the local Analysis services model (i.e., the user in the AS model that has the RLS configuration applied to it)

You can check if the RLS is working by tracing the query statement through SQL Server Profiler

like this:

You can use the above information to check if the mapping is working, if it is working and make sure that all the above configurations are correct, RLS will work correctly!

Below are some related documents, hopefully they will be helpful to you.
URL:
Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn
Dynamic row-level security with Analysis services tabular model - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ · Accepted solution]

Hi @Maieev 

What you can do is to use the user mapping feature to your on premise analysis services cube.

Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn