Forum Discussion
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
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..
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).
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.
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.
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.
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.
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:
- 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.
- Create a new function in SQL Server that checks the relationship between user and allowed sales territories.
- 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:
- 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.
- 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.
- 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.
