topic Re: Row Level Security (RLS) on SQL Server data in Service

Row Level Security (RLS) on SQL Server data

Anonymous — Thu, 02 Jun 2016 08:40:13 GMT

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.

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.

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

Re: Row Level Security (RLS) on SQL Server data

ankitpatira — Thu, 02 Jun 2016 09:02:06 GMT

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

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Thu, 02 Jun 2016 13:49:36 GMT

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

Thanks

Kaz

Re: Row Level Security (RLS) on SQL Server data

ankitpatira — Fri, 03 Jun 2016 00:50:43 GMT

Anonymous Try this,

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

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Fri, 03 Jun 2016 01:21:26 GMT

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

Re: Row Level Security (RLS) on SQL Server data

ankitpatira — Fri, 03 Jun 2016 05:01:12 GMT

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.

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Fri, 03 Jun 2016 05:21:59 GMT

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

Re: Row Level Security (RLS) on SQL Server data

Vicky_Song — Mon, 06 Jun 2016 08:42:11 GMT

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.

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Mon, 06 Jun 2016 08:47:18 GMT

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

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Wed, 15 Jun 2016 09:02:07 GMT

bump...

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Mon, 20 Jun 2016 07:41:01 GMT

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

Re: Row Level Security (RLS) on SQL Server data

GuyInACube — Mon, 20 Jun 2016 13:28:03 GMT

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.

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Thu, 07 Jul 2016 02:50:13 GMT

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.

Thanks

Kaz

Re: Row Level Security (RLS) on SQL Server data

Anonymous — Thu, 07 Jul 2016 03:28:20 GMT

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