cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Row Level Security in Power BI Report Server

Hi, I am trying to implement 'Row Level Security' in Power BI On premise-solution

1. Creating roles in 'Microsoft Power BI Desktop for Report Server - October 2017' works fine in desktop but then there is no option to add users to these specific 'Row Level Security groups' in Power BI report server. In Power BI online, there is option to add users/ groups once reports are published from desktop to cloud. I cannot see similar option in report server

2. I tried to create a SQL table and tried to implement row level security dynamically by reading data. The table is in below format

 

UserId               SecurityGroup

domain/test1    USA

domain/test2    Europe

domain/test3    Asia

Then I created a Role which filters based on username() in 'Microsoft Power BI Desktop for Report Server - October 2017' Again, this is working fine in 'Microsoft Power BI Desktop for Report Server - October 2017' but when I "SAVE AS" the report to "Power BI Report Server" then the security is not working All users are able to see data for all countries. I am providing 'Browser' role to the users in report server. Any idea what I am doing wrong in the set-up? Or how can I implement row level security in reports saved to Report Server?

28 REPLIES 28
Highlighted
Microsoft
Microsoft

Re: Row Level Security in Power BI Report Server

Hi @nitin_goel05,

 

Please have a look at my reply in this similar thread.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Row Level Security in Power BI Report Server

As far as I know still, it was not implimented, but we can use another way to create a measure as given below

filterRLS =

CALCULATE(
COUNTROWS(Data),
Data[UserName] = USERNAME())

 

above DAX,  Data is the table name and Data[UserName] is the filed. User name format is "<domain\username>". after creating the measure you can filter using report level as FilterRLS =1.

 

it will filter according the current user.. enjoy..

 

 

  

 

Highlighted
Helper I
Helper I

Re: Row Level Security in Power BI Report Server

Highlighted
Regular Visitor

Re: Row Level Security in Power BI Report Server

I setup the measure like you mentioned, but am having troubles applying it as a report level filter.

 

I am able to set report level filters with table columns, but measures seem to not be allowed.  I drag the measure over to the report level filter section but it will not take the measure.  Would you please explain a little more on how to do this?

 

Thank you in advance 🙂 - This has been a real PITA, and I wish Microsoft would just add RLS to Report Server (On-Prem).

Highlighted
Anonymous
Not applicable

Re: Row Level Security in Power BI Report Server

We have succesfully implemented Row Level Security, however we have had to create a SSAS Tabular Model to do it. Hopefully there are plans in the future to make Row Level Security easier.

 

The article below uses a good example as to how it can be done (same way as we implemented it):

 

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

 

Hope this helps.

Highlighted
Regular Visitor

Re: Row Level Security in Power BI Report Server

Thanks for the info jdenne.  It's too bad they haven't added Row Level Security into Power BI Report Server yet.  I guess we will go down this route and see if it will work for what we are trying to do without creating too much additional work.

Highlighted
Helper I
Helper I

Re: Row Level Security in Power BI Report Server

We're using a different method that implements RLS at the database level for Power BI reports.  This is in SQL Server 2016.

 

You can't pass the logged-in user ID to SQL Server.  However, if you use 'Windows Authentication' / 'As the user viewing the report' on the Data Source Settings for the report, you can use the SYSTEM_USER function to get the user ID within SQL.

 

We created a permissions table and a number of security predicates in our DB, using SYSTEM_USER to determine the logged in user.  I like this method better than building filters in the report itself because you only have to build the security predicates once; every report you build using those secured tables automatically gets RLS, instead of having to incorporate it in every report.

 

I think security predicates are only available in SQL Server 2016 and later.  In earlier versions you could set up stored procedures or views as the data source for your reports and build the RLS login in those directly.

 

A potential drawback with this approach is every report user needs a SQL login.  We got around that by creating an Active Directory group, giving the group the SQL login, and putting all the report users in the group.  That worked well for us because we were already using the AD group for other purposes and we had processes/procedures set up to maintain the group membership.

Highlighted
Frequent Visitor

Re: Row Level Security in Power BI Report Server

@davidm5 after moving RLS implementation to DB level, do you still require Power BI Pro license to use this RLS (db) feature?

Highlighted
Helper I
Helper I

Re: Row Level Security in Power BI Report Server

No, in this scenario the report consumers don't require a Power BI license.  The report developer still requires a Pro license to publish the report to the server.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors