RLS in on premise SSAS tabular live connection with PowerBI Report Server on premise

[MerelKentalis]

hi all,

I cannot find any documentation nor solutions in the community on the following (but maybe I'm not searching well enough).

I have an on premise SSAS Tabular cube with RLS using the folowing DAX

=AUT_Kostenplaats[VerantwoordelijkeMedewerkers]=USERNAME()

This AUT_Kostenplaats table has a bi-directional relationship to a DIM-table with a single relationship to the fact table.

We use Power BI Report Server on premise storing the reports on the server and letting our end users use these reports using the web browser. So no PowerBI Service.

I think we've done everything right, but it doesn't work.

All I have found so far on RLS combined with the Tabular is on PowerBI Service, and all I have found on RLS with PowerBI Desktop is without the tabular...

Hope someone can help! We're really struggling here...

Thanks in advance!

[MerelKentalis]

After a whole lot of double checking the RLS seems to work.. now we go back to the drawing board to discuss how we are going to combine low detail security with high level of data security.... 

[d_gosbell]

There is nothing special about the SSAS tabular and Report Server combination. Report Server should just work like any other client tool. Have you tested that your role is working with other client tools like Excel or Power BI Desktop?

[MerelKentalis]

I've been trying some more. Using Power BI desktop (not the version for RS but de free license version) connecting to my test tabular with RLS I can see it working. So that's good news 🙂

However, that's not how we work.. So my next plan is to try to get this test cube with RLS restored on our PBIRS on production and try store a new report based on that cube on de report server and then acces it via the web. I would expect to see the same result, but somehow I have my doubts..

[d_gosbell]

What do you mean by "it doesn't work"?

If your users see no data then it probably means that the usernames in your table are not in the same format as the value returned by the USERNAME() function.

If your users see all the data you need to make sure they are not in multiple roles or that they are not admins on the SSAS server (admins bypass RLS security). If you run a SQK profiler trace against the SSAS server while users run the report and listen for the "Session Initialize" and "Existing Session" events the text column for those events will have a list of the roles applied to that user. If you see a * symbol in the role list the user is an admin and will not get any of the role filters applied.

[MerelKentalis]

Hi,

The problem is that they see everything, so the RLS doesn't seem to kick in. My appologies for not clarifying that at the start.

We've (of course) checked that USERNAME() equals the values in the AUT table. We've also made sure that the users do not exist in the default group with the default role and they are certainly not admins on SSAS. I will double check this again to make sure we didn't overlook anything (that would be really stupid...).
The other things you mention are hocuspocus to me (sorry, not so very technical) but my colleague might understand so I'll pass this onto him.

Do you know of any documentation that is related to my situation? So the SSAS tabular RLS - PowerBI Server on premise combination? I can't seem to find any.

Thanks for your help and quick response. Much appreciated!