cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ctijsseling Regular Visitor
Regular Visitor

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?

 

 

5 REPLIES 5
Ctijsseling Regular Visitor
Regular Visitor

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

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?

samyrmoises Frequent Visitor
Frequent Visitor

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

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!  

stpnet Established Member
Established Member

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

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

fbackes Regular Visitor
Regular Visitor

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

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!

vgudipati_pbi Occasional Visitor
Occasional Visitor

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

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?