cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexeyRusinov Helper II
Helper II

Enterprise Gateway with Analysis Services not passing logged in users credentials

Hello everyone,

I am trying to set power BI live connection to SSAS data warehouse. I need to set security model, so I create different roles and users in SSAS instance. When open power BI from non-admin user, I see in SQL profiler that connection is made under admin user, and effective user name is passed, so everything seems right.

But the user in Power BI sees all the data, not reduced by his security roles. I followed articles Link 1 and Link 2 , but it doesn’t help.

My problem is similar to this, Enterprise Gateway with Analysis Services not passing logged in users credentials , except passing effective user name does not help to see reduced data.

 

Anyone can help, what else should I check or set, to use SSAS security model in Power BI?
Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
AlexeyRusinov Helper II
Helper II

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Hello everyone,

 

The issue's fixed now. The user was admin on the server, and in SSAS config there is a property <BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>.
As a result, even if the user is not shown as an admin in SSAS, he was, because of server admin and this property. 
So, either changing property to 0 or removing user from server admins solved the issue.

Thank you everyone who tried to help. 

View solution in original post

10 REPLIES 10
Super User III
Super User III

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

@AlexeyRusinov Are you using SSAS Tabular or Multidimensional? Your first 2 links refer to MD, and your final one Tabular...


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
AlexeyRusinov Helper II
Helper II

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

@Seth_C_Bauer I'm using Tabular with InMemory now.

 

Actually I'm following this description row level security with SSAS tabular, except that it doesn't work for me. I run the MDX query in Management Studio, logged in under a non-admin user - the query returns reduced result. I'm doing the same thing in Power BI (show list of IDs in table visualization) - it returns the whole list...

saagarkinja Advocate I
Advocate I

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Dear Alexey,

 

Thanks for sharing the details.

 

Please note below points,

 

  1. Role Level Security is configured at Visual Studio end or at SSMS end.
  2. If created at Visual Studio, please enter required Dax formula for filter and add user in User tab. Then you may Analyze in Excel and test for specific user on same.
  3. If created at SSMS end, please install SSMS on specific user logged-in computer, then connect to SSAS model and test.

This on-premise testing on data will validate RLS at Database end.

 

If success, the troubleshooting will be done at Power BI end.

Kindly let us know, if you face any issue.

Also, please share the screenshot for better understanding.

 

Thanks,

Saagar Kinja

AlexeyRusinov Helper II
Helper II

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Hello @saagarkinja, thank you for your response.

 

I created RLS at SSMS end, here are my testing results in pictures:

1) The role is created in SSMS

 

Member.jpg

2) .. with row filter

Filter.jpg

 

3) MDX query works fine

MDX fine.jpg

 

4) but Power BI doesn't

BI_disappointment.jpg

 

5) SQL Profiler shows correct EffectiveUserName

profiler.jpg

 


What did I miss?

 

 

saagarkinja Advocate I
Advocate I

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Thanks for sharing these details.

 

So, the RLS is working on On-Premise DB, but not working on Power BI Service.

 

If Power BI is connected live with SSAS Tabular model, the RLS should work. I implemented same at 3 different occassions and working fine.

 

Sorry, cant help further.

 

Please share the the solution(in future received) of this issue.

 

Thanks,

Saagar Kinja

Moderator v-qiuyu-msft
Moderator

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Hi @AlexeyRusinov,

 

Based on my test, add the row filter for the user on SSAS side, this user can only see the data which is allowed when connecting to the tabular both on Power BI Service and Power BI Desktop. In your scenario, please check if this user is added into the administrator group on this server which has SSAS installed. Also check if the user has only read permission like below:

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlexeyRusinov Helper II
Helper II

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Hi @v-qiuyu-msft , thank you for your reply.

 

The user is added as an administrator on the server indeed and given read permission on SSAS.

 

Additional info:

the filtering works fine in PBI Desktop with live connection, but not working in app.powerbi.com. What could be the issue? Enterprise Gateway set wrong? I haven't seen an option to set the Live Connection option in Gateway, only in Power BI Desktop. Maybe it is set to import? Can I check it?

 

cc @saagarkinja

saagarkinja Advocate I
Advocate I

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Dear Alexey,

 

I would like to add few things,

Request you to add the User in RLS at Visual Studio with Read access and a specific DAX filter and another with Admin access.
Analyze in Excel to check same.(just another step)
Process>Deploy
Connect Power BI Desktop Live with SSAS Tabular Model with Admin Account. (Please note the configured RLS might not work when connected by Import method through Admin person.)
Create any sample report > Publish to Power BI Portal to a workspace where the Read only User is added in group.
Try logging into Power BI portal by Read only user account. Check if the it works or not.

If your Power BI Gateway shows connection successful and you are able to refresh it, no issues with Gateway.

 

Please note, you need Pro License access, Access of user in workspace and access at tabular model.

 

Please do share you findings.

 

Thanks,
Saagar KInja

AlexeyRusinov Helper II
Helper II

Re: Enterprise Gateway with Analysis Services not passing logged in users credentials

Hello everyone,

 

The issue's fixed now. The user was admin on the server, and in SSAS config there is a property <BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>.
As a result, even if the user is not shown as an admin in SSAS, he was, because of server admin and this property. 
So, either changing property to 0 or removing user from server admins solved the issue.

Thank you everyone who tried to help. 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors