topic Re: Row Level Security works on Desktop but not on Server Portal in Report Server

Row Level Security works on Desktop but not on Server Portal

cgaralde — Wed, 14 Dec 2022 07:33:11 GMT

HI,

I have recently configured our on-prem report server, created some reports and TRIED applying RLS on the same reports unsuccessfully. RLS does work when I try VIEW AS on the desktop app but when I deploy it on the portal and run the report, the values are just blank.

Here is the premise:

I have the following table to control access to certain data called, ACCESS CONTROL.

User | Company | Store

DOMAIN\user.name | MyCompany 1 | MyStore 1

DOMAIN\user.name2 | MyCompany 1 | MyStore 2

I have also setup a measure to check for the current user,

User2 = USERNAME()

And then I created Role on the desktop app called, PERMISSIONS.

That is filtering on the ACCESS CONTROL table using the following

[User] = [User2]

Finally, I added two slicers, one to show Company and the other to show Store. If I try this setup on the desktop app using VIEW AS, i get my SLICERS filtered correctly.

I uploaded the report on the portal and ran it only to be greeted with access errors on all visuals. I, then, added the user on the report with the same role as PERMISSIONS and when I ran the report again I can see all reports but the SLICERS are blank.

So, in the end did I miss something which is why me slicer is not showing correctly. I would appreciate all input regarding this matter.

Re: Row Level Security works on Desktop but not on Server Portal

alosada — Wed, 14 Dec 2022 08:54:58 GMT

hi!

When using dynamic RLS by username() you should apply this RLS in Power BI Desktop as follows:

- Select other user option as well, and enter the desired email.

There you will see if the RLS is really doing it's job.

What I also recommend you is to use directly the USERNAME() function in the role DAX expression.

Re: Row Level Security works on Desktop but not on Server Portal

cgaralde — Wed, 14 Dec 2022 09:08:37 GMT

alosadaThanks for your input. 🙂

I have done this earlier and RLS still works on the desktop if I select both OTHER USER and PERMISSIONS. 😞

Re: Row Level Security works on Desktop but not on Server Portal

alosada — Wed, 14 Dec 2022 09:12:34 GMT

And that happens to you or the user you are trying to apply RLS?

Re: Row Level Security works on Desktop but not on Server Portal

cgaralde — Wed, 14 Dec 2022 10:03:56 GMT

I have setup two users and both appear correctly in the desktop app when i enter the user as DOMAIN\user.name in OTHER USER.

Re: Row Level Security works on Desktop but not on Server Portal

alosada — Wed, 14 Dec 2022 10:13:07 GMT

And on the portal that happens to all users or only you?

Have you trying deleting the dahsboard from the portal and reuploading it? (not rewriting)

Re: Row Level Security works on Desktop but not on Server Portal

cgaralde — Wed, 14 Dec 2022 10:23:57 GMT

I prayed so hard hoping that this is the soultion but alas, I deleted the report, reuplaoded it and then I configured the permissions in the report server and I still did not get any data on my slicer. 😞

Re: Row Level Security works on Desktop but not on Server Portal

cgaralde — Wed, 14 Dec 2022 10:49:11 GMT

I finally found the issue and corrected it and it finally works.

First of all, this DAX website, https://dax.guide/username/ states that USERNAME() returns DOMAIN\username and based on this my table checks for DOMAIN\username. Now, I have added a CARD VISUAL on my report to show USERNAME() and lo and behold, on the desktop app, it shows DOMAIN\username but on the portal it shows username@DOMAIN.COM.

Thanks alosada for all you inputs. Surely your replies guided me in the right direction.

Re: Row Level Security works on Desktop but not on Server Portal

AlainJimenez — Mon, 22 Jan 2024 16:26:35 GMT

Try to set up the Row Level Security property of your report. In my example below I selected the role "Authorizer" then added the members who will access the report