How to do Row Level Security in PBI Report Server using live connection from SSAS cube

[rincewind]

The data in SSAS cube. The cube has 1 flat table. No dimensions. All the relevant data is in this table. The reports are housed in the Power BI Report Server

The table has a column called "ClientName" which lists the clients. The ClientName column has values like APPLE, GOOGLE, REDDIT etc. There are a about 8 clients like these. The goal of RLS is that we have a common report to be viewed by all users at all the clients' companies but the people at APPLE should only see data for the rows that have ClientName as Apple, and so on.

We created 8 Active Directory groups for the 8 clients. And users from the client companies were added appropriately to them. 

For the actual RLS these are the steps I have followed.

1. Created Roles on the SSAS cube using SQL Server Management Studio.
2. Assigned only 'Read' permissions to the role
3. In the members tab, added the appropriate AD group to the role. 
4. In the Filters tab, assigned the filter using DAX. For example for the Apple role, the expression was ='TABLENAME'[ClientName]="APPLE"

A sample report was created with the ClientName column in a matrix visual. This report was uploaded to the Power BI report Server.

In internal testing, this worked perfectly. We created a test cube that was identical to the production cube. Added a couple people from our own company to each of these AD groups. We assigned these AD groups to roles in the test cube. They were able to see only the ClientName assigned to them. 

We then tried testing with actual client users. Created a test report with a single visual as above with the report connected to the actual production cube. But they were able to see all the values in the column.  

I then tried to repicate the initial test by asking the initial internal users to test the first sample report. (Connected to test cube). But they were able to see all values in the column as well. Whenever I do "Analyse in Excel" in Visual Studio, I get the intended results in all the tests. (RLS is applied) Both on the test cube and the production cube

I'm not sure what I'm doing wrong. 

Some more information:

1. In Manage options for the report, Data Sources > Credentials > Authentication Type is Windows Authentication. And the option "use the following credentials" is selected. A username and password has to be entered in the field there. This was provided by the admin team. I'm not sure what the use of this is but thought I should mention.
2. In Manage options for the report, Data Sources > Connection type is Analysis Services. The Connection String is appropriately edited as below

Data Source=SERVERNAME;InitialCAtalog=CUBENAME;Provider=MSOLAP.8;IntegratedSecurity=SSPI;Impersonation Level=Impersonate;

As far as I know, none of the users have been added to any of the administrators/developers AD groups. I'm not sure what I'm doing wrong. Please advise.

Thanks

[d_gosbell · Accepted solution]

---

@rincewind wrote:

Some more information:

1. In Manage options for the report, Data Sources > Credentials > Authentication Type is Windows Authentication. And the option "use the following credentials" is selected. A username and password has to be entered in the field there. This was provided by the admin team. I'm not sure what the use of this is but thought I should mention.

---

This is most likely your issue. When you have it configured this way the only user the cube sees connecting is the one entered in the data source. 

When using RLS you should either use the credentials option of "As the user viewing the report" (although this may require that your IT department configure Kerberos Authentication)

Or the other option is to try clicking the checkbox under the "Use the follow credentials" option that says to try impersonating the current user. The most comprehensive write up I've seen of this option is here http://www.artisconsulting.com/blogs/greggalloway/2010/4/1/using-effectiveusername-to-impersonate-in... and although the screenshots are from an older version of SSRS I think the core mechanism still works the same.

[rincewind]

thank you! @d_gosbell What you suggested worked! I just had to check that one box and Row Level Security started working!

Thanks again!

[elayda]

.

[d_gosbell]

---

@elayda wrote:

Hello, I have the same problem, i'm a beginner, i can't solve the problem of rls for report server and active connection, it's my graduation project can you help me please

---

If this is the same problem then the same solution should work.

[elayda]

.

[d_gosbell]

---

@elayda wrote:

For me I create Roles in the multidimentional cube SSAS, I test them in excel it works but when i publish my report in power bi rs, i don't know how can I add my roles and test them. 

---

You don't need to re-create the roles. As long as you have not configured the PBIRS connection with a fixed identity and the user identity is passed through then the cube will enforce the roles

[rincewind]

 I remember distinctly that the option you mentioned is not checked. I will make the changes on it and report back. Thank you for taking the time to read and help.