Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SSAS Tabular connection to Power BI doesn't pickup role

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?

 

 

8 REPLIES 8
suvadeepbhuyan
Regular Visitor

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?

 

Anonymous
Not applicable

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 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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?

Anonymous
Not applicable

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!  

Anonymous
Not applicable

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

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!

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors