On-premise SQL Analysis Service vs PowerBI Service

[Anonymous]

Hello

I have on-premise SQL Analysis Service connected by Power BI Enterprise Gateway to PowerBI Service. In PowerBI Service I use domain account (lets say: domain\powerbigateway) in Data Source Settings to connect with PowerBI Gateway. Everything working fine if this account domain\powerbigateway is Server Administrator on SQL Analysis Service. If I remove this account from Server Administrator role and I am trying to add this account on any other role on cube like on this screen:

I am loosing connection between SQL Analysis Service and PowerBI Service and on report I am getting a message:

I found in docs (https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-ssas) this note: "The Windows account you enter must be a member of the Server Administrator role on the Analysis Services instance you're connecting to. If this account’s password is set to expire, users could get a connection error if the password isn’t updated for the data source. To learn more about how credentials are stored, see Store encrypted credentials in the cloud."

So.. looks like this account must be Server Administrator on SQL Analysis Service but...how does that relate to least privilege security principle etc.? For me read privilege is enough to read data from the source.

Is there any way or workaround to limit these permissions? I am not sure is a good idea to have technical account as Server Administrator.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

1 First you need to understand the role types in SSAS.

Why must be Server Administrator on SQL Analysis Service to connect the service, because only the admin allow to connect. The account under Data sourece setting must be a admin role in SSAS. It is only used to connect.

2  the role(permission) in SSAS

In power bi service, you only need create a read role(permission). When power bi user read the data from on-premis SSAS via the report, the power bi service will sent the user’s account (like email address) to the SSAS. Then if the account (email address) in the role , the SSAS will send back the data (applied the RLS ). I suggest you create a role and only tick the read permission. Then set up RLS in Role Filter.

3 Users under data source setting : who can ues this data sourece. For example.

4 power bi service user only can use read permission

5 where to edit permission. SSMS or Visual Studio.

Have I explained myself?

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Read permission is not sufficient. You also need to have Discover permission.  Not sure if that can be set separately from the admin permissions?