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

Row level security doesn't work when using 'Both' cross filter direction

We are trying to apply Row Level Security (RLS) using Power BI RS using the January 2019 Release that now supports it.  The security filter doesn't cross filter to the transactions.  I have a security filter on the responsibility table and I can see that the responsibility table is filtered but the trasactions are not (even with the Cross filter direction set to 'Both').  I cannot filter the transactions directly because of the one to many relationship between cost centres and people who have access to them.

 

Using Power BI Online, it works when I tick the "Apply Security filter in both directions" box.  If I select that for Power BI RS then I get the following error when I deploy to Power BI RS:  

2019-01-29 10:41:16.0611|ERROR|21|Failure in reportproperties| RequestID = s_3be2f858-5351-48ac-999b-64a42b8dcde2 Microsoft.PowerBI.ReportServer.AsServer.AsConnectionException: Failed to publish model for temp_d0f51fea-9ba6-4da2-b125-f8f31a36b3ed ---> Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: 'Table 'Cost Centres' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behavior on relationship 'e34ab282-bf84-459b-8286-2fbec5025020' cannot be Both.
'.
   at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions)
   at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.AddRole(String roleName, String databaseName, String filter)
   at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.<>c__DisplayClass23_0.<LoadDatabaseInAsAsync>b__0()
   --- End of inner exception stack trace ---
   at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.<>c__DisplayClass23_0.<LoadDatabaseInAsAsync>b__0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.<LoadDatabaseAsync>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.ReportServer.WebApi.PBIX.DataModelArtifactsProvider.<RetrieveArtifactsAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<ShredWithModel>d__34.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<ShredFromPreshreddedFiles>d__24.MoveNext()
2019-01-29 10:41:16.0611|INFO|28|Sending response. Response code NT AUTHORITY\NETWORK SERVICE 400, Elapsed time 0:00:00.2476884| RequestID = s_3be2f858-5351-48ac-999b-64a42b8dcde2 

I cannot share the original model but I've prepared a mockup that shows the issue here.  Donwload it, change the itmanager@test.com.au email in the Responsibilities table to your email, save it to the RS, add yourself to the manager role and test.  Ideally you should only see data for CC 200 and should not be able to see data for CC 100.  In this case you see everything.  When you click on the filtered responsibility then the transactions do filter (the cross filter works in interactive mode but not using the security filter).  If you publish the model to Power BI Online with the "Apply Security filter in both directions" set on the Responsibility to Cost Centre relationship then you should see that it works (after adding yourself to the manager role and testing with that role).

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power BI Team rpatkar
Power BI Team

Re: Row level security doesn't work when using 'Both' cross filter direction

We have issued a fix for this issue in the latest build 15.0.1102.235. Please upgrade to the latest build available.

Thanks,
Rohit

View solution in original post

12 REPLIES 12
praseejbk Frequent Visitor
Frequent Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

Hi

 

I am presently using PBIRS january - 2019 version also using January - 2019 Desktop version. I tried RLS which is not working for me. I used a colmn with User Names and  in Manage Roles [User Name] = USERNAME() in order to filter base on the user names. when I try view as roles it work fine. but in server nothing filtered. configuration of PBIRS as Manage-> row level security -> add member.

 

if any one tried and success please let me know.

 

Regards

Praseej   

Mithrilendil Frequent Visitor
Frequent Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

Hello,

 

Desactivate RLS on your report, add a measure with username() formula and publish the report to see what username() looks like.

 

On desktop (in my environment) the result of username() is DOMAIN\login and on the server it is login@DOMAIN.fr

kstough Visitor
Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

I'm seeing the same behavior as @pietercvdm. I've confirmed that the filter he's setup (using USERPRINCIPALNAME()) is correct, but irrelevant to the issue.

 

Removing the security filter on the relationship between Responsibilities and Cost Centres (and ensuring the proper UPN exists in the Responsibilities table) allows the upload to succeed. I can see the Responsibilities table filtered as expected, but then the other tables naturally aren't filtered.

 

 

Highlighted
Power BI Team rpatkar
Power BI Team

Re: Row level security doesn't work when using 'Both' cross filter direction

We have issued a fix for this issue in the latest build 15.0.1102.235. Please upgrade to the latest build available.

Thanks,
Rohit

View solution in original post

pietercvdm Frequent Visitor
Frequent Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

Hi Rohit

 

I just saw it.  I'm busy updating now and will let you know how the test goes.

 

Thank  you for the update.

pietercvdm Frequent Visitor
Frequent Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

Success!  Thank you for the fix for this and the direct query issue.  We were struggling to roll back and was going to restore from backup in order to roll back.  Now we've just upgraded with the new patch and all is good (both direct query and security cross filtering).

 

Kind Regards,

Pieter

ToddChitt Member
Member

Re: Row level security doesn't work when using 'Both' cross filter direction

@rpatkar

I just downloaded and installed the Febuary 2019 Power BI Desktop. I too am having an issue with RSL in my model and for the life of my cannot figure out what is going on. Let me describe the general model layout:

 

* Fact table (20 million rows) with with a dimension key value. 

* Dimension table. Dimension Key is unique (as it should be)

* Security table which lists Users (username@domain) and the Dimension members (dimension key) to which they have access. 

* One-to-Many relationship from Dimension table to Fact table, with SINGLE Cross Filter direction

* One-to-Many relationship from Dimension table to Security table, with BOTH Cross Filter direction AND checked the box for "Apply security filter in both directions"

* Security Role defined with the following DAX statement on the Security table: "[UserPrincipalName] = USERNAME()"

 

In theory, the Security table gets filtered via RLS and the USERNAME() function, and the BOTH Cross Filter direction then filters the Dimension table, which then filters the Fact table.

 

Now here's the issue: Facts are added (or more likely Dimension members are removed) in such a way that there are orphaned facts that list a Dimension Key that no longer exists. (not my choice for data integrity, but that's the case.)

 

In THEORY, those orphaned Facts should NOT show up for ANYONE governed by RLS in the Role.

In FACT (no pun intended) the orphaned records show up for EVERYBODY governed by RLS. 

 

I removed Incremental Refresh from the fact table and refreshed it in its entirety, no change. 

 

This is driving me nuts. It's like there is a BUG. 

 

FYI: I moved my RLS DAX Expression to the Dimension table with the following:

 

CONTAINS (
     'Security Table',
     'Security Table'[Dimension Key],
      USERNAME(),
     'Security Table'[Dimension Key],
     'Dimension Table'[Dimension Key]
)

 

and that seems to have fixed it. But still, WHY would the Bi-directional filtering not work?

 

Any ideas would be appreciated. 

 

 

jodycalvert Frequent Visitor
Frequent Visitor

Re: Row level security doesn't work when using 'Both' cross filter direction

@rpatkar Microsoft had an issue with RLS the other day on their support page under Awareness:

Some customers may experience issues adding users to RLS roles on the RLS Security tab. As a potential workaround, customers can refresh the page and add the user. Engineers are working on a fix and expect it to be deployed to all regions by end-of-day 3/29/19.

 

RLS began working again on 3/26; however, as of yesterday, some of my users are getting the following message when trying to view a report where we had to check the "Apply security filter in both directions" box for one of our relationships:

 

“The user belongs to multiple roles that have security filters which isn't supported when one of the roles has filters affecting table with Security Filtering Behavior = Both relationships"

 

Has this always been the case, or did the “fix” from January come undone when they fixed the RLS on 3/26?  Thanks!

Power BI Team rpatkar
Power BI Team

Re: Row level security doesn't work when using 'Both' cross filter direction

hi Jody, can you please provide the link for the support page awareness text you are quoting from? Does it apply to the Service (aka powerbi.com) or the Server (aka PBIRS)? This thread has been about Server related issues. Thanks.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 3,387 guests
Please welcome our newest community members: