cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MatthewV10 Frequent Visitor
Frequent Visitor

Power BI On Premise - Applying Row Level Security

Hi all,

 

I have recently set up a Power BI On Premise Report Server. From there I created a report in Power BI Desktop and saved it on to the report server, this report is using Direct Query to do a simple select from one of our databases. The report works fine and I can view it both in the report manager and via a URL directly in the browser.

 

I'm now trying to implement Row Level Security, to do this I'm using Manage Roles and setting up a DAX expression linking USERNAME() to a field in my dataset. This all works perfectly in Desktop, I can View Roles etc. and I am very happy, but now when I try to open the report on the report server or via a URL in the browser I'm just getting the following error:

 

This visual contains restricted data

To view this visual, contact the dataset owner to request access to the data behind it.

 

From my searching around I honestly can't find much information, but the searching I've done seems to suggest that it isn't possible to do what I want to do (although it is old information so I just want to check that nothing has changed):

 

I have also published this report to the Power BI Service, using that the Row Level Security looks good and I can switch users and see the data changed.

 

The ideal scenario is to be able to externally access the on premise report server and have security work correctly based on USERNAME() / USERPRINCIPALNAME() (which are both returning FirstName.LastName@domain.com)

 

Any help would be greatly appreciated!

 

Thanks,

 

Matthew

1 ACCEPTED SOLUTION

Accepted Solutions
SuraMan Regular Visitor
Regular Visitor

Re: Power BI On Premise - Applying Row Level Security

@MatthewV10 

 

Please check that you have done the following step.

 

After the report is uploaded to PBIRS;

 

  • Go to the PBIRS folder that pbix is residing.
    Each pbix is shown in a tiled view with 3 dots on the top right hand corner.
    Click those 3 dots and select "Manage" -> "Row-level security"
    Click "Add Member"
    Here, you have to add AD usernames or AD groups to the security-role you defined in PB Desktop.
  • Instead of adding each user/role, I think "Everyone" group can be added here, so that Row Level Security can appropriately filter rows for each user.

 

View solution in original post

10 REPLIES 10
Microsoft gboreki
Microsoft

Re: Power BI On Premise - Applying Row Level Security

We shipped RLS support with the January release. But in order to see data you need to add the user to one of the roles in the report (even for the user who is updating the report), follow the steps in the "Add member to Role" section in this link:

https://docs.microsoft.com/en-us/power-bi/report-server/row-level-security-report-server

 

This is not very explicit, but its called our as part of the limitations on that page.

 

Let me know if you still runs into problems.

 

Thanks

-G

MatthewV10 Frequent Visitor
Frequent Visitor

Re: Power BI On Premise - Applying Row Level Security

So I have done that, I have a UserName field which I set to FirstName.LastName@domain.com.  I then take that UserName field and set up a Role called UserSec where I enter the DAX UserName = USERNAME().  I click the tick and this is fine.  Following this I uploaded the report to the On Premise Report Server and all of a sudden I cannot see any data, I just get errors saying that the user does not have permission.

 

If I go to the Power BI Service and login in I can set up RLS in the Security tab and then the security works as it should (in the Power BI Service), but I want security to work directly on the On Premise Report Server as using the Power BI Service defeats the point for us.

 

What I need to do is:

 

1) Create a report in Power BI Desktop

2) Add a Role to that report (using DAX) that links a field to the USERNAME()

3) Save the report from Power BI Desktop to the Power BI On Premise Report Server

4) View the report on the Power BI On Premise Report Server with RLS active

 

Is that possible?

MatthewV10 Frequent Visitor
Frequent Visitor

Re: Power BI On Premise - Applying Row Level Security

Just a quick bump, does anyone know anything about this please?

PowerBI_Develop Frequent Visitor
Frequent Visitor

Re: Power BI On Premise - Applying Row Level Security

I found out, that Power BI Report Server Version 1.4.6969.7395 (January 2019) converts the result of DAX function USERNAME() to

USERPRINCIPALNAME()

In PowerBI Desktop create a card visual that only shows the a measure called USERN.
Measure USRN looks like:
USRN:=username()

Deploy this report to PowerBI Report Server (January 2019) and you will see the result is:
User@upnsuffix and NOT Domain\username
SuraMan Regular Visitor
Regular Visitor

Re: Power BI On Premise - Applying Row Level Security

@MatthewV10 

 

Please check that you have done the following step.

 

After the report is uploaded to PBIRS;

 

  • Go to the PBIRS folder that pbix is residing.
    Each pbix is shown in a tiled view with 3 dots on the top right hand corner.
    Click those 3 dots and select "Manage" -> "Row-level security"
    Click "Add Member"
    Here, you have to add AD usernames or AD groups to the security-role you defined in PB Desktop.
  • Instead of adding each user/role, I think "Everyone" group can be added here, so that Row Level Security can appropriately filter rows for each user.

 

View solution in original post

Highlighted
SuraMan Regular Visitor
Regular Visitor

Re: Power BI On Premise - Applying Row Level Security

Hi @PowerBI_Develop 

 

Thanks for confirming this. I was wondering why my PB reports are not showing the USERNAME() correctly. Is this a bug?

MatthewV10 Frequent Visitor
Frequent Visitor

Re: Power BI On Premise - Applying Row Level Security

@SuraMan 

 

Thank you so much for your answer!  I'm thrilled it is now working but I'm really annoyed that I had missed that.

 

When viewing the report server from my PC with my main user account I only saw Open in the list, I was surprised at first to not see Manage (as I'm used to using SSRS) but I just figured that everything was controlled in Power BI Desktop.  It was only going on the server as another account that I could then follow your advice and find Manage...

 

As said, thank you very much, done a bit of testing and it looks to be working as expected! 🙂

mannou78 Occasional Visitor
Occasional Visitor

Re: Power BI On Premise - Applying Row Level Security


@PowerBI_Develop wrote:

I found out, that Power BI Report Server Version 1.4.6969.7395 (January 2019) converts the result of DAX function USERNAME() to

USERPRINCIPALNAME()

In PowerBI Desktop create a card visual that only shows the a measure called USERN.
Measure USRN looks like:
USRN:=username()

Deploy this report to PowerBI Report Server (January 2019) and you will see the result is:
User@upnsuffix and NOT Domain\username

Notice the same thing can someone confirm please if this is a bug and if it was raised to Microsoft

 

Thanks

QMintrup Frequent Visitor
Frequent Visitor

Re: Power BI On Premise - Applying Row Level Security

Any update on this?  It appears that username() and userprincipalname() are still routing to the same result once published.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)