on-premise data gateway with live connection to analysis service cube: problem with effective user

[Anonymous]

Dear Community

In Power BI Desktop, I have created a report that makes a live connection to an Analysis Service cube and I have published this report to powerbi.com. Since we want to use role-based security, we need to pass the effective user to the cube. According to this article by "GuyInACube" Adam Saxton, the value should match the UserPrincipleName (UPN) for Analysis Service Live connections. However, if I do this, on powerbi.com, I can't see any data and an error message "There was a data source access problem. Please contact the gateway administrator". I believe that the connection from the on-premise data gateway uses ADOMD to connect to the cube, I have therefore looked up Connection String. According to this article on Microsoft Docs, the EffectiveUserName has to be used when an "end user identity must be impersonated on the server. For SSAS, specify in a domain\user format. For Azure AS, specify in UPN format. To use this property, the caller must have administrative permissions in Analysis Services." In fact, if I manually map the user in the data source user mapping to DOMAIN\user, the connection works!

Question 1:

• Can someone confirm that for on-premise data gateway and locally installed Analysis Service, and a report with Live connection to this cube, the user name has to be of the form DOMAIN\user and cannot be the UPN?

I do not want to map hundreds of users manually for each data source, and I have therefore edited the file Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config to make a lookup on the Active Directory, as described on Microsoft Docs. Unfortunately, in our AD, there is no attribute with the value DOMAIN\user, but only an attribute containing the user (samAccountName). 

Question 2:

• Is it possible to edit the config file in such a way that I can statically add the "DOMAIN\" to a dynamically retrieved "user"?

Thank you very much for your help!

Andreas

[v-deddai1-msft]

Hi @Anonymous,

>> Can someone confirm that for on-premise data gateway and locally installed Analysis Service, and a report with Live connection to this cube, the user name has to be of the form DOMAIN\user and cannot be the UPN?

No, you can use the  UPN instead of DOMAIN\user. But you really need to check if the email address you log on to power bi should match a defined UPN within the local Active Directory Domain, if a match cannot be found, you will get errors.

You can use the following command from your workstation to find out the UPN for your account :

whoami /upn

For more details, please refer to : https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#error-data-source-access-error-please-contact-the-gateway-administrator

>>Is it possible to edit the config file in such a way that I can statically add the "DOMAIN\" to a dynamically retrieved "user"?

I suggest you use Azure AD Connect tool synchronizes local accounts to Azure AD tenants. Please refer to https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-ssas#synchronize-an-on-premises-active-directory-with-azure-ad

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft 

Thanks for your answer. Unfortunately, I do not fully understand your answer. When you say "you can use a defined UPN" instead of DOMAIN\user, how does this go together with the documentation on Microsoft Docs concerning Connection String properties for Analysis Services ADOMD.

I have to check again with our IT department, but I believe we already use the Azure AD Connect, since my user name I use to logon to powerbi.com matches my UPN, according to whoami /upn.

Thanks and kind regards,

Andreas

[v-deddai1-msft]

Are you sure that your login name is the same as the upn that you found with the command? Or you need to map it in the datasource.

[Anonymous]

Yes, I am sure, unless it is case sensitive (see below)

Does the gateway service account have to be in the same domain as the user? Or is it sufficient it has access to the active directory?

Thanks

Andreas

[v-deddai1-msft · Accepted solution]

You need to change the srevice account of gateway to domain user .