There was a data source access error. Contact gateway administrator error on the report

[Hemanth_PBIuser]

Hi Team,

Good day !!!

We are migrating reports from "domain-1 to "domain-2". In the migration part we have some of the reports which are pointing to SSAS tabular model (Live connection) and some are directly connected to DB engine. I am able to access SSAS Tabular model live connection on my power bi desktop with my windows credentials ("domain-1") then I published to the power bi service (service account - "domain-2").

For configuring a gateway, I set up the on premises gateway on the "domain-1" server using the "domain-2" user accout. Now I am able to access the gateway on my "domain-2" service account and able to configure gateway clusters for connectivity with "domain-1" servers using windows credentials. Tried to configured scheduled refresh for Import mode SQL database reports which is working fine but there was an error ("There was a data source access error. Contact gateway administrator error") when I am connecting with SSAS cubes. 

Please help me with build the connectivity between power bi service "domain-2" with the SSAS server on "domain1". Suggest me if there is an better way to connect SSAS cubes from "domain-1" to "domain-2". 

Thanks,

Hemanth

[Hemanth_PBIuser]

Hi Team, 

Thanks for your support !

I am able to solve the issue using map user names option. Now I am facing different issue when trying to access the SSAS cubes with same user, recently my account password expired so i resetted the password. After password update I am facing the below issue.

Unable to update connection credentials. The on-premises data gateway's service account failed to impersonate the user

Details: IMSOnPrem: Received error payload from gateway service with ID 80743: Error logging on username 'user@domain.com'.. Gateway_Production: Received error payload from gateway service with ID 80746: Error logging on username 'hd@merkleinc.com'.. Please have this information handy if you choose to create a support ticket. ActivityId: d05f4ced-47cf-4080-b550-5e54255fda85 RequestId: 2cfc9115-9503-4e2a-a185-e32e24a373fc Cluster URI: https://api.powerbi.com Status code: 400 Time: Mon Jul 31 2023 12:25:19 GMT+0530 (India Standard Time)

Please help me ASAP. Thanks in advance.

[Hemanth_PBIuser]

@v-yueyunzh-msft Thanks for your support. It worked for me. 

[v-yueyunzh-msft · Accepted solution]

Hi , @Hemanth_PBIuser 

Based on your issue description and my research, actually the SSAS data source is a special data source which is different from other data sources published in the Power BI service, when using SSAS, each time a user interacts with a report connected to Analysis Services, the effective user name passes to the gateway and then passes on to your on-premises Analysis Services server. The email address that you use to sign in to Power BI passes to Analysis Services as the effective user in the EffectiveUserName connection property.

The email address must match a defined user principal name (UPN) within the local Active Directory (AD) domain. The UPN is a property of an AD account. The Windows account must be present in an Analysis Services role. If a match can't be found in AD, the sign-in isn't successful. To learn more about AD and user naming, see User naming attributes.

User Naming Attributes - Win32 apps | Microsoft Learn

And for this special data source, Power BI also design an easy for the "Mapping user name" for the gateway server when we try to use the AAD account signed in to be mapped to the defined user principal name (UPN) within the local Active Directory (AD) domain access the SSAS when using on-premise data gateway refresh data or query data:

You can configure custom UPN rules in Power BI for Analysis Services data sources. Custom rules help if your Power BI service sign-in name doesn't match your local directory UPN. For example, if you sign in to Power BI with meganb@contoso.com but your local directory UPN is meganb@contoso.local, you can configure a mapping rule to pass meganb@contoso.local to Analysis Services.

For the steps description and detailed explanation, please refer to this official document:

Manage SQL Server Analysis Services data sources - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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