topic Re: SSAS Tabular connection to Power BI doesn't pickup role in Report Server

SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Tue, 13 Mar 2018 14:29:03 GMT

Hi,

I have this problem: I have created a SSAS Tabular 2016 model with a role called: "ThisIsASecurityRole". This "role" use the following DAX filtering: =Users[Login]=USERNAME() on the Users table (Table columns: PK, Login). The goal is to have a second table, the TimeRecorded table, filtered by users.

I use the following connection string:

"Data Source=ServerName\InstanceName;Initial Catalog=RowLevelSecuritySSASTabular;Roles=ThisIsASecurityRole".

When I connect to Power BI using the above connection string (I amend the connection string in PBRS/Manage/Data Sources/Connection String), the Power BI report works but do not filter the data.

Additional details:

When I connect to Excel or SSRS (a SSRS report on the same PBRS server, in the same exact folder) using the same connection string, the reports are filtered correctly. So I believe that my SSAS model is correct.
When I connect with a test user that don't have any elevated right, he still get all the data in Power BI unfiltered (but the data is correctly filtered using Excel or SSRS). In order to connect using this user, I use remote desktop to another server using this test users credential.
The PBRS and the SSAS Tabular model is on the same server.
The SQL Profiler trace shows a difference between the way the SSRS report and the Power BI one is processed:
- SSRS trace has the following:
  - EventClass = Session Initialize
  - TextData = "thisisasecurityrole"
  - At the end of the XML Text: <Roles>ThisIsASecurityRole</Roles></PropertyList>
- Power BI trace has the following:
  - EventClass = Session Initialize
  - TextData = "*,thisisasecurityrole"
  - At the end of the XML Text: </PropertyList> (no Roles tag even though the connection string has such a tag).
I've created the Power BI report using a live connection to the SSAS Tabular 2016 described above
( Home/Get Data/Analysis Service/"Connect Live"/Model) using the appropriate Server and Database names. I then saved the report on PBRS. This is the only source in the power bi report.

What steps do you think are missing in order for Power BI to filter my report according to the role called 'ThisIsASecurityRole" defined in SSAS Tabular 2016?

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Tue, 20 Mar 2018 08:27:03 GMT

UP. So nobody has an answer to this query? This is the 3rd or 4th of such post - the others are created by other people - and nobody seem to know whether this is a bug or if there are way around this?

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Thu, 09 Aug 2018 22:18:20 GMT

Hello, Guys.

Do you know if this issue still persist? I'm on a job exactly like this one, and I'm not facing a solution!

Thanks Indeed!

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Mon, 13 Aug 2018 08:40:52 GMT

We've managed to implement this succesfully. I can't quite see what's different about what you have done and what we have done. I think they are more or less the same.

I do think you may be hitting an issue with the test account you are using. You say you are remoting onto a mahcine as that user, so it probably has fairly elevated privilages.

The info shown in the original posts includes

TextData = "*,thisisasecurityrole"

the * indicates the user is a member of the admins groups and this is probably causing your security to be ignored, hence the reason the user is seeing everything.

regards

Re: SSAS Tabular connection to Power BI doesn't pickup role

fbackes — Wed, 10 Oct 2018 20:22:16 GMT

That exactly was the problem in my case! The administrator role supersedes all other roles, eliminating all RLS conditions.

Many thanks for pointing that out!

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Thu, 18 Apr 2019 10:35:10 GMT

Please let us know how you were able to resolve this issue?

How * has been removed in the roles and PBIRS consider only the role of login user?

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Wed, 24 Apr 2019 07:42:27 GMT

Have you tried using an acocunt that isn't a SSAS Admin?

In your session initialise event you get

EventClass = Session Initialize
TextData = "*,thisisasecurityrole"

The * indicates you are an admin on the model and as such the security may not be behaving the way you expect. We have to use separate dummy accounts when testing this stuff for this exact reason.

Re: SSAS Tabular connection to Power BI doesn't pickup role

Anonymous — Thu, 05 Nov 2020 03:41:47 GMT

Hi,

I am facing the same issue and data is not getting filter with even hard coded value.

Tested Steps:

1. Browsed on SSAS Tabular Database on RDP loged in domain user.

2. On Security Contex model - 3 Options I) Current User II) Other User III) Roles

When i Select III option and filter works fine with selected Role

But when I try with I Or II options did not filter any data and shows all data and even i traced in sql profiler RLS.

Please share us any thing missing or share us some document if you have resolved .

Thanks,

Suresh

Re: SSAS Tabular connection to Power BI doesn't pickup role

suvadeepbhuyan — Fri, 12 Mar 2021 20:26:33 GMT

Has this been resolved? We are facing the same issue where even a non admin group business users (assigned properly to the SSAS role> member) sees all the data unfiltered on a Power BI report through the gateway. The same user while access the SSAS model from an Excel pivot sees only limited data restricted to his/her role. Isn't this way to long to be still not resolved by PBI?