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

[Anonymous]

Hi All,

All of a sudden we are getting this error message

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

Error Message :

Unable to connect: This data source cannot connect to any gateway instances of the cluster. Please find more details below about specific errors for each gateway instance.Hide details

Activity ID:	77ec383e-efa0-48a8-976d-1274b707d73c
Request ID:	e02730b7-bc97-ec69-0678-6798d355066c
Cluster URI:	https://wabi-us-gov-iowa-redirect.analysis.usgovcloudapi.net/
Status code:
Time:	Fri Feb 12 2021 12:43:13 GMT-0500 (Eastern Standard Time)
Service version:	13.0.15350.49
Client version:	2102.1.04813-train COI On Premise Gateway: The on-premises data gateway's service account failed to impersonate the user. Please need help Thanks

I changed the DS Credentials to my user name and my windows password and the connection was successfull , but still i get the error message when i try to RUN the report. Source is SSAS Cubes.

The error i am getting when i open the report is 

This report cannot access the datasource . Contact Joe Smith the author.

[v-stephen-msft]

Hi @Anonymous ,

To get the Gateway up and running again I did the following

1. Changed the account running the Gateway from the default service account to a domain user. If I remember correctly, this process will let you to, either repair or register a new Gateway. I registered a new Gateway with a new name.

2. Ran a test of rthe Gateway successfully.

3. Setup the same data sources for the new Gateway, both SQL sources and file sources.

4. Changed the published reports to use the new gateway and with the new datasources

5. Republished the reports from Power BI desktop

6. In same cases I had to re-share reports to specific persons in the organisation, to get them to use the report again. For reports published in Teams channels, no more action needed.

If you don't need it, just disable: Use SSO via Kerberos for DirectQuery queries:

After unticking the SSO option my datasets are refreshing successfully again.

The above method is from this post: https://community.powerbi.com/t5/Issues/on-premises-data-gateway-s-service-account-failed-to-impersonate/idi-p/935868/page/2#comments

Best Regards,

Stephen Tao

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

[agarwal137]

Faced same issue on migrating to a new laptop. The SQL datasources were not connecting on Power BI Service resulting in failed reports refresh. However, files on sharepoint could connect and refresh. The error thrown was -

After 2 days of trying everything, it somehow got solved. Here's how - 

- Ensured .NET is installed on system

- Installed Microsoft Access Database engine 2010

- Installed MS ODBC driver 17 for SQL server

- Uninstalled and reinstalled On Premises data Gateway.

- Did a reset of Domain/Laptop Login Password

- Selected the 'existing gateway' for setup (which was being used earlier to run reports in old laptop)

- After setup, logged into Gateway using domain email id.

- Ran couple of reports on Excel connected to the same SQL server databases

- Then Closed everything and did a system shut down.

- Restarted system and found that the datasources were back up on Power BI servcie!

Note that I checked the data source connection status after each step and it wasn't up until the last. So not sure if all are relevant but then here's how it got solved for me.

[Anonymous]

Thanks @Stephen Tao and @lbendlin , but why does it affect only one report and not the others. This is the only report that is using SSAS as the source.

[lbendlin]

usually that message means that the connection user's password has changed, and the fix is to update the gateway connection credentials accordingly (NOTE: the dataset's credentials are unrelated but probably need refreshing too.).