Row Level Security (RLS) on SQL Server data

[Anonymous]

Hi everyone, 

I am trying to give RLS a test but not getting anywhere when end users access reports & dashboards. Here's what I have set up, and done. 

Data: 

Data is coming from SQL Server through Reporting Services on-prem. The dataset contains sales data for districts in Western Australia, and contains two queries, Corporate Sales & Calendar.

I have added a new column UserID in Corporate Sales query in Query Editor and assign pbitest001 & pbitest002 based on random criteria. so now the rows in this dataset belongs to two users, pbitest001 & pbitest002.  I created a report, and in 2nd tab, i dropped a matrix, plotted user id and district fields on columns, and revenue figures on columns. basically this matrix will tell me what user i am able to see after applying RLS in the cloud.  I published the report in cloud, and dont' set up auto refresh, as yet.

PBI Service:

After publishing the report, I went to the dataset properties > security > RLS. Created a new rule in Corporate Sales which says [UserID] = "pbitest002", saved, and tested the role. It just shows the data for pbitest002 user. Sweet. 

 RLS DAX ruleRLS rule test

End User (PBITEST002)

When I log on to cloud using PBITEST002, i can still see data for PBITEST001. The column DaxUserName is a DAX measure, set to return value of Username(), which in this case returns current user name. 

Data view of logged on user (member, non admin)

Not sure why 002 user is able to see data for 001? I tried fully qualified column name in the DAX expression set in rules but does not work. What's more confusing is that the test works as expected. 

any ideas folks?

Thanks

Kaz

[Vicky_Song]

@Anonymous, did you check the same Power BI report when you do "Test data as role" and sign in as PBITEST002? The images you posted show that you're checking Power BI report when you do "Test data as role", however, you're checking with Power BI Dashboard when sign in as PBITEST002. 

[Anonymous]

@Vicky_Song, yep, I checked the same report at the time of testing the role as well as after logging in as PBITEST002. 

[Anonymous]

bump...

[GuyInACube]

I just did a test on my end using the same fields as in your screenshot. I didn't encounter any issues. It worked as expected. I tested with both of the following DAX expressions for the rule.

[UserID]=username()

[UserID]="janedoe@guyinacube.com"

These both filtered data as expected.

It may be worth opening a support ticket to see what is happening on your end, as it isn't obvious.

[Anonymous]

Hi Adam,

Where did the filters work for you? Did they work at the time of testing, within the dataset security where you have the feature to 'test as role'? or did it work for you in an actual report? I have added screenshots (below) where I expect to see pbitest002 user to see only Central District, but the user can see all districts.

How can I log a support ticket? I have just tested out RLS after June 2016 update, and it has not changed a bit for me.

1 - Implementing RLS and testing it on Desktop (June 2016)2 - Configuring RLS in cloud (post publish) and adding users to role3 - Viewing as the end user

Thanks

Kaz

[Anonymous · Accepted solution]

Hi Adam, and everyone, 

I finally got it t work. the reason why RLS was not working as expected, was that the role member (PBITEST002) was configured as an admin of the workspace. RLS started working as expected once I set pbitest002 as member.

Thanks

Kaz

[Anonymous]

Um, looks like this feature is not there yet... 

[ankitpatira]

@Anonymous When you created role in RLS did you added your user PBITEST002 under Members tab ? Follow steps listed in this post. 

[Anonymous]

@ankitpatira Yep, I did add PBITEST002 to the role members list. 

Thanks

Kaz

[ankitpatira]

@Anonymous Try this,

in your DAX you don't need to mention table name. so your dax should be [Column]="PBITEST002"

[Anonymous]

@ankitpatira Thanks Ankit. I tried using just the column name, and still does not work.. PBITest002 is still able to see rows for PBITETST001, though it works well at the time of testing RLS. 

Thanks

Kaz

[ankitpatira]

@Anonymous we have to troubleshoot this further. In Test Data as Role, you can also enter individual email address and preview for that, so enter PBITEST002 email address and check what it shows you in preview.

[Anonymous]

@ankitpatira already did that, and it works at the time of testing it, as it is illustrated in the RLS test image in my original post. 

Thanks

Kaz