Enterprise Gateway with Analysis Services not passing logged in users credentials

[AlexeyRusinov]

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

[AlexeyRusinov · Accepted solution]

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. 

[apetersen]

Hi Guys

We having the exact same issue, problem is we have RLS security implemented on our tabular model (we have a security table setup on sql with these particular email addresses) we then deploy to from the tabular model to SSAS and  then hit a live connection on PowerBI. Now the problem is when logging on to POWER BI desktop or web with the user (non admin) we still seeing all rows and filters? When checking this in excel the filter applies? Our On Premise gateway is setup with the "admin user yes" but surely Power BI is smart enough to fiugure this out.

Steps we have taken

- User in Question is Read only on SSAS properties

- is not a member of the admin group on SSAS

- ON premise gateway and not personal gateway

[Seth_C_Bauer]

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

[AlexeyRusinov]

@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]

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]

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

2) .. with row filter

3) MDX query works fine

4) but Power BI doesn't

5) SQL Profiler shows correct EffectiveUserName

What did I miss?

[v-qiuyu-msft]

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:

Best Regards,
Qiuyun Yu

[AlexeyRusinov]

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]

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

[saagarkinja]

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