Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nitin_goel05
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
davidm5
Helper I
Helper I

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.

@davidm5 Can you explain step by step process. How can we use SYSTEM_USER in SQL Server?

All right, here's my best attempt to walk through it.  For this example I’ll be using the AdventureWorksDW2012 database.

 

For my hypothetical case, I’ve got regional sales directors that should only be able to see information for employees that are in their assigned sales territories.  I’m going to implement that by putting a security policy on the DimEmployee table.  In summary the steps are:

  1. Create and populate a new table that defines which sales territories a particular user (as defined by their Windows login ID) is allowed to access.
  2. Create a new function in SQL Server that checks the relationship between user and allowed sales territories.
  3. Create a new security policy on the DimEmployee table using the function created in step 2.

Once those steps are done I can create a report in Power BI that accesses the DimEmployee table and take advantage of the implemented RLS.

 

In more detail:

 

  1. Create a new table that defines which sales territories a user may access.  I called my table SalesTerritoryRLS.  It’s a simple table with two columns that looks like this:

NetID

SalesTerritoryKey

Davidm5

1

Davidm5

4

SomeOtherUser

2

 

The NetID column is simply the Windows user ID of the user, and SalesTerritoryKey correlates to the column SalesTerritoryKey in the DimEmployee table.  So as currently populated this table says that I (davidm5) am allowed to see data for sales territories 1 and 4; the user ‘SomeOtherUser’ is allowed to see data for sales territory 2.

 

  1. Create a new function in SQL Server. The SQL to create the function looks like this: 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE FUNCTION [dbo].[fn_SalesTerritory_SecurityPredicate] (@salesTerritory AS int)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

       SELECT 1 AS fn_SecurityPredicateResult

       FROM dbo.SalesTerritoryRLS rls

       WHERE rls.SalesTerritoryKey = @salesTerritory

              AND rls.NetID = SYSTEM_USER

GO

 

We’ll apply this function as a filter in the next step.  Basically this function checks to see if the sales territory on a row it’s evaluating is one of the sales territories the currently logged in user is allowed to access.  That’s where SYSTEM_USER comes in – see the final line of the WHERE clause.  SYSTEM_USER is the ID of the currently logged in user.

 

  1. Create a security policy using the function. The SQL for this is:

CREATE SECURITY POLICY [dbo].[dimEmployeeFilter]

ADD FILTER PREDICATE [dbo].[fn_SalesTerritory_SecurityPredicate]([SalesTerritoryKey]) ON [dbo].[DimEmployee]

WITH (STATE = ON, SCHEMABINDING = ON)

GO

 

This ties the function created above to the DimEmployee table.  When you do any SQL operation on the table, it passes in the SalesTerritoryKey for each row and the function is used to determine if it’s a row the user is allowed to access, based on the values in the RLS table created in step 1.

 

Now you’re free to access DimEmployee in your Power BI report and RLS will be enforced.  Note that for this to work you MUST use ‘Windows Authentication’/’As the user viewing the report’ on the Data Source Settings for the report.

 

Notice that creating the function and applying it as a security policy are two different steps.  That means you could apply that same function to multiple tables to implement our RLS on sales territory across multiple tables if you’d like.

 

Also be aware that this means RLS is applied at the data layer; for any tool the user might use to access the DimEmployee table, RLS will be applied.  Even if I log into SSMS directly to query the DimEmployee table, I’ll only be able to see rows where the sales territory is 1 or 4.  And the way I wrote this particular function means that by default users get NO access to DimEmployee data; until a row is entered for them in the SalesTerritoryRLS table, they won’t be able to see any DimEmployee data.

 

Anonymous
Not applicable

davidm5, this is the method we've got running on our database layer, RLS is enabled for the currently connected user. The only issue we're having is passing the logged in user's credentials to the database via the report in the Report Server (on-prem). Do you have a similar set up or any insight this double-hop authentication? 

I'm about out of my depth here, but I'll try to help.

 

When you set up the report on Report Server, when you go to Manage > Data Sources and look at the Credentials section, you MUST use 'Windows Authentication' as the Authentication Type, and you MUST use the 'As the user viewing the report' option.

 

Then in SQL Server, for whatever database you're using that same user ID must have permissions on the database.  I think just db_datareader is all you need but I'm not certain about that.

 

The only trick we do is to use an active directory group.  This is where I'm sketchy, but if I understand it correctly we actually give the AD group the SQL permissions, then just add the Windows user ID to that AD group.  It isn't necessary to do it this way, it just makes our user management a little easier.  We already have mechanisms in place to handle adding and removing users from the AD group as employment and roles change - by using the AD group we don't have to seperately manage SQL permissions per user ID.

 

I hope that helps.  If not, if you can provide details I can try and provide more info.

Anonymous
Not applicable

Hmm. Thanks Davidm5, but we've already got that setup. We have a group saved into the DB Server and have datareader permissions set for the required DB.

 

The  following link in a previous thread describes our setup: DirectQuery-Login-failed-for-user-NT-AUTHORITY-ANONYMOUS-LOGON.

 

I'll clarify the problem by saying that the DirectQuery and RLS stuff works - but only when viewing the report via Chrome. Using IE throws either a Kerberos constrained delegation error, or 'connection string not properly formed' error. So even though we're looking at the same report, different browsers behave differently.

 

Internet options have been set on IE and the site has been added as 'Intranet' level security. 

 

Smiley Frustrated

 

 

I'm sorry I don't have an answer for you, but I can tell you that it's working in IE for us so I can confirm it's possible.  I don't know what magic the networking folks might have done behind the scene to pull that off though.

Anonymous
Not applicable

No problems davidm5, we managed to figure it out, thanks for your contribution.

 

For those of you still struggling with Kerberos authentication between an SQL Server (2016) and On-Prem report server, here is a few things to check when following the Kerberos configuration article posted on the MSDN.

 

  • Ensure your user has access to the datasource with datareader permissions, either via a dedicated logon or as part of a group logon.
  • Ensure that you set up constrained delegation for the  (domain) user accounts that run SQL Server and Report Server
  • Ensure that you have enabled constrained delegation on the server accounts themselves (i.e. the Computer's AD Entry must allow for Kerberos Authentication)

 

 

That last, bolded part is what had caused us headache. From our perspective, it seems that the MSDN article missed a few steps..

 

 

I'm trying to do something similar with the SQL server security table but ran into problems. 

 

I have my table of users and can filter the SQL queries fine using the current user. However, when I run a scheduled refresh of the data in Report server it uses the specified credentials and so returns no data. 

 

I tried creating a measure using CurrentUser = USERNAME() and then filtering the model by this but it won't let me use this measure in a table filter.

 

I want to refresh the data with the admin credentials and return all rows, then filter by the current user.

 

Any suggestions?

If I understand what you're trying to do, you're NOT using a security policy in SQL Server for RLS.  I say that because that technique doesn't work if you're doing scheduled refreshes, it only works for direct query.  If I've misunderstood, my apologies.

 

So with the assumption I understand what you're trying to do, here's an approach:

 - You'll need a table in your report that links user ID's to whatever the filter field is.  Let's call it SalesTerritoryRLS.  It only needs to have two columns, one with the user ID and the second with the filter value.  It's the table I gave in step 1 of my example earlier in this thread.

- Create a measure that looks like this:  filterRLS = CALCULATE(COUNTROWS(SalesTerritoryRLS), SalesTerritoryRLS[NetID] = USERNAME())

- Now add a visual level filter to the visual that's displaying the data you want to filter where filterRLS is 1

 

I've done this as proof of concept, but not tried it in anything that's been deployed to a large set of users, so it's possible there are problems with this approach I've not run across.  In my proof of concept it worked fine.

 

Thanks @davidm5.

 

So if I have a person with 2 territories in the Territory table how will this filter display both territories? 

 

So for example, if I have a pie chart and want to filter it to only show the regions the user has in the SalesTerritoryRLS table, I would set the filter to filterRLS >= 1?

 

Thanks.

No, you should still use filterRLS = 1.  You just need a row per sales territory/user in the filter table.

 

The measure is evaluated per row of data being displayed in your visual, so as long as there's a single row in the filter table with that sales territory/user combination the measure should still evaluate to 1.  This is working for me in my proof of concept.

Great that works perfectly. I was just unsure about how it was evaulating it.

 

Can I use that measure in a slicer? So it filters my slicer to only show the matching Territories?

I think what you want is to have a slicer that shows only the sales territories that the logged-in user is allowed to see, that you want to do that by showing the sales territory in the slicer and filtering the slicer using the filterRLS measure.  Problem is that you can't put a visual filter on a slicer.

 

There may be some kind of hack or workaround, but not one I'm aware of off hand.  Best I can think of is instead of a slicer, having a table that lists all the sales territories and when the user clicks on a row in that table it filters everything else on the page.

Anas
Frequent Visitor

Hi,

 

I applied the visual filter and it works but when the report is published in the report server, the end user has the filter pane that can use to cancel the filter applied for each visual.

 

Your idea is great but is there any way to avoid the end user to edit the filter from the front end report.

 

thank you for your help

Unfortunately not that I know of.  That is a huge problem with this approach.  It's more a Row Level Suggestion than actual Row Level Security - a convenient way to pre-filter a report for users, but not true security.

 

 

Anas
Frequent Visitor

Hi,

Thank you for your reply, I was looking if there is a way to use a measure as report level filter or as slicer.

A slicer can be used in hidden page ans sync to be applied to all the report.

But I'm unable to find a way to use a measure as slicer.

Hey,

 

I'm not sure if you are aware of this recent post by Christopher Finlan that you can find here:

https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-release-timing-update/

 

Here it is stated that with next release of Power BI Report Server that is scheduled for January 2019, it will then support Row Level Security 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks for your help @davidm5.

 

I'll have to find a workaround for the filters. 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.