On-Premises Data Gateway's service account failed to impersonate the user

[JaneLHunt]

We have an on-premise Gateway Server runing on Windows Server 2016 standard.

A data source was set up connecting to on premise SQL Server.
The data source uses Windows authentication.
The Domain Name\account name is entered (This is our own windows service account in Active Directory used for data refreshes on multiple SQL Server data sources in the Power BI Service to avoid personal accounts running data refreshes)
We do NOT have anything ticked in the data source setup concerning SSO and Kerberos with directquery.

The SQL Server data source with this account has been working fine until this morning.

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

Details:

Power BI On Prem Gateway: Received error payload from gateway service with ID 38035: Error logging on username DOMAIN\accountname'..

Please have this information handy if you choose to create a support ticket.

ActivityId: 0b5d6236-f7af-4d0b-bae4-0264d324f60c
RequestId: 5ec11144-590e-4922-a0c8-b838a921dd5f
Cluster URI: https://api.powerbi.com
Status code: 400

I have checked connectivity from the Gateway Server using this WINDOWS account with SSMS and it can login and select data, no problem.

I Updated the Gateway Server to the latest February 2023 version (released yesterday).

Still the same problem.

I can create SQL Server data sources using my personal account, but am no longer able to create a SQL Server data source using this specific account.

Out of ideas as to what to do next - there are no connectivity issues between the server the Gateway is on and the SQL Servers it needs to log into. The problem seems to be specific to this particular Windows account when trying to set up a SQL Server data source in the Power BI Service.  The error message seems to imply that the Gateway Server's service account cannot impersonate this particular account set up in the data source, which it was able to do up until today.....

Is there anybody who could kindly help?  Many thanks

[JaneLHunt]

Hi @GilbertQ - many thanks for replying.

Do you mean change the Gateway Service Account (the default NT Service\PBiegwService) to be the same as the Domain\NonUserSpecificAccount we use in our SQL Server data sources? Why would this change be needed now as the SQL Server data sources are set up to use Windows authentication with the DOMAIN\NonUserSpecificAccount and the SSO boxes have NOT been ticked.  So the Gateway Service Account should not be trying to impersonate the User account anyway?

Up until yesterday when the problem appeared, we had changed nothing our end - either on the Gateway or on the SQL Server databases we connect to.
I updated the Gateway Server yesterday from the previous release (December 2022) to the latest release (February 2023) in the hope the problem would be rectified, but still the issue persists.

There has definitely been no change on the database side concerning permissions - I have checked this and tested connectivity from the Gateway Server with SSMS, logged in as this DOMAIN\NonUserSpecificAccount.

I have tried creating new SQL Server data sources connecting to various on premise SQL Server databases which I know the on premise non user specific Windows account could connect to in the past and each time I get the same confusing error message.  Why would the error say that the on premise Gateway Server's service account could not  impersonate the user when it should not anyway as the SSO boxes are not ticked?

If I use my personal on premise DOMAIN\Windows account, I can create a new SQL Server data source without any issues.  So not sure why there is now this difference in behaviour between these two accounts is now occurring.  Confused‌‌!😕