Forum Discussion
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 [email protected] 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).
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
13 Replies
- praseejbkAdvocate I
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
- MithrilendilFrequent Visitor
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 [email protected]
- AnonymousNot applicable
I'm seeing the same behavior as Anonymous. 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.