Direct Query Report - RLS User getting 401 error when accessing in App Workspace or Shared Report

[Anonymous]

We are running into an error with a report developed with DirectQuery mode on a SQL Server Connection.  We have configured the report RLS with two security groups:

• Group A which is filtering on a user table with UserName = UserPrincipalName()... user table is tied to a location which filters the reports by location
• Group B which is has no dax filters giving anyone in this group access to all locations

Admins of the App Workspace can see everything fine as if they are unfiltered

Members that are assigned to security Group A and security Group B are able to see the report, but they get errors in all tiles.

Status Code401

Activity ID3ead3220-90c5-4c92-b721-847c893bb8a1

Correlation IDaae44a61-de15-c843-bb10-088def95092e

Request ID1f4739f6-4d64-bba8-9347-7ee8c524be88

TimeMon May 14 2018 14:23:39 GMT-0500 (Central Daylight Time)

Version13.0.5248.195

Cluster URIhttps://wabi-us-north-central-redirect.analysis.windows.net

Can someone please help?

Thanks,

Bryan

[GilbertQ]

Hi there,

Has it ever worked with going down to the SQL Server source?

I have only done Direct Query RLS to an SSAS Server, so not exactly sure how it would work with SQL Server

[Anonymous]

It works as expected in Power BI desktop when testing the Security Roles. Not sure why it wouldn’t work if security can be applied with DAX on a table.

[GilbertQ]

Hi there

Have you added the users to the Security Role in the Power BI Service?

[Anonymous]

Thanks for your reply and working through this with me. I was under the assumption this was controlled by AD Security groups named the same as the role you create. Where are users added to the role in PBI Service.

[GilbertQ]

Hi there,

Ok that is for imported datasets, I forgot that yours was DirectQuery.

The only other thing to check for is to ensure that your gateway is mapping the usernames correctly through to your SQL Server source.

I would run Query Profiler on your SQL database and check to see if it is passing through the correct username?