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

[monika_todor]

Hi, 
Have this issue been solved?
I have the same problem and the solution you are suggesting does not seem to work my end (On the web page to manage the Gateways and Data Sources, add ?newManageGatewaysUI=false to the end of the url).
Can please anyone advise on this asap?

Thank you!

[Michael_Mertens]

Hi, the issue "passwords with non-English characters" was solved. So in our installation I can use the standard user interface to add passwords with non-English characters. And indeed, the old interface doesn't seem to be available any more. 
Maybe there are other causes for the same symptom?

[JaneLHunt]

Hi - I confirm that the old workaround no longer works as the old interface is no longer available.  The only workaround currently that we know of to resolve this is to change the password so it consists of English/ASCII printable characters......

[DSQU]

 We are having the same problem the OP described as well. Trying update URL is not working as well. 

[DSQU]

we ended up just resetting our password even though it looked to be all english characters, and that solved the problem. 

[monika_todor]

The password solved the problem our end as well. Thank you everyone!

[monika_todor]

So there definitely isn't a solution to this which I consider as a HUGE problem (the password I'm using consists of English characters and numbers).

[Michael_Mertens]

Hi Jane, 

I'm very thankful that you posted this workaround. We have exactly the same issue and ?newManageGatewaysUI=false works!

Regards

Michael

[JaneLHunt]

Hi @Michael_Mertens .  Glad this helped resolve your problem! 

I still have a ticket open with Microsoft Support concerning this issue. 

I don't know if it's the same for you, but found that this problem only occurred when using service accounts that had passwords with non-English characters and using the new Gateway user interface, so therefore had to use the old gateway user interface.   If the service account had only English characters in the password, then it worked fine with the new Gateway web interface.

[Michael_Mertens]

Thank you also for this additional information. It looked like the problem would occur with non-personal-accounts and would not occur with personal accounts, but indeed the non-personal-one has a non-English character, the personal one didn't. 

[JaneLHunt]

Hello @Michael_Mertens 

Just to let you know - Microsoft have come back to me - they have now got to the bottom of the problem - it's to do with how passwords are encrypted.

They expect the bug fix to be deployed out by 9th July hopefully.
In the meantime, to quote Microsoft Support:
As a workaround, please use the old managed gateway experience or use a simplified password with no mixed characters until this fix is deployed.

[Michael_Mertens]

Thanks again for the update!

Regards, Michael

[JaneLHunt · Accepted solution]

WORK AROUND - use the OLD web interface for managing Gateways and Data Sources

After working with Microsoft Support, the following has been uncovered and a Work Around Provided:

New Gateway Experience in the Service:
Setting up a data source using a Windows Service Account specially created to use with data source to login to SQL Server to refresh data.  This Windows Service Account was created to avoid storing someone's personal Windows Account in the data source. - error message in Service :

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

Looking in the gateway logs this error message was found:
Invoke-Sqlcmd : A connection was successfully established with the server, butthen an error occurred during the login process. (provider: SSL Provider,error: 0 - The certificate chain was issued by an authority that is not trusted.)

However - checking the server certificates, there are NO problems with them and are up to date

Old Gateway Experience in the Power BI Service User Interface - setting up Data Source successful with NO Errors
On the web page to manage the Gateways and Data Sources, add ?newManageGatewaysUI=false to the end of the url.  This will open up the old web interface.  Add the data source to the Gateway, and the data source is created with no errors.

The issue has now been sent back to Microsoft to investigate further.

[Adamboer]

It seems that the issue is related to the service account that is being used to connect to the SQL Server. You mentioned that you have checked the connectivity from the Gateway Server using the Windows account with SSMS, and it can log in and select data without any problem. This suggests that there might be an issue with the permissions for the service account.

One thing you could try is to check if the service account has the necessary permissions to connect to the SQL Server. Also, check if there have been any changes made to the account or the SQL Server recently. You could also try creating a new Windows account and setting up a new data source with that account to see if it works.

If the issue persists, you might need to reach out to the Power BI support team and provide them with the details and error messages you are receiving. They should be able to help you diagnose the issue and provide a resolution.

I hope this helps!

[JaneLHunt]

Hello @Adamboer . 
Thanks for your reply. 
I have checked that the service account can connect to the SQL Server and it definitely can and it has the necessary login and associated user permissions to read data from the database I am trying to connect to.
I can connect to the database and select data with SSMS running as this service account on the Gateway Server, so there are no apparent firewall issues.......And this service account has been working fine for the past few years refreshing our datasets and dataflows via the Gateway!
There have been no changes to the account or SQL Server as far as I an aware, having checked with Active Directory Admin (and I'm a DBA!),  so odd that it just stopped working, which suggests to me that it may be something to do with changes behind the scenes in Power BI/Gateway Service or a Microsoft Update on the Gateway Server (Windows or Gateway update).
I have created a new data source using this account and no joy, still same error message concerning the fact that it cannot impersonate the account.
The issue just seems to be when I try to use a Windows service account (an on-premise Active Directory Windows account which is then sync'd to Azure Active Directory with password expiration disabled). I have tried another service account  and get the same problem.  The service accounts have the appropriate Power BI pro license to run dataset refreshes in a group workspace.

Thanks anyway - looks like I will need to contact Microsoft support - something I have tried to avoid as it can be quite a time consuming process, especially when dealing with connectivity type issues!
  

[imbarr]

Hope this may be of some help as I have has similar issues.

I run the gateway service as the default NT SERVICE\PBIEgwService, so looked in the log files under C:\Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway on my on premise gateway server. In the latest GatewayErrors20230305.000000000.log I found the following error,

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError

GatewayVersion=

InnerType=UnauthorizedAccessException

InnerMessage=<ccon>Access to the path 'C:\Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway' is denied.</ccon>

InnerToString=<ccon>System.UnauthorizedAccessException: Access to the path 'C:\Windows\ServiceProfiles\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway' is denied.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

   at System.IO.FileSystemEnumerableIterator`1.CommonInit()

   at System.IO.FileSystemEnumerableIterator`1..ctor(String path, String originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler`1 resultHandler, Boolean checkHost)

As a test I have given ‘Everyone’ read permissions to that folder and straight away the failing data source connections can be brought online and I can use the original accounts to connect.

At the moment I can't say for certain that it ia as a result of me running the February 2023 update that caused the failure or maybe a change on our servers, but it does seem a bit of a coincidence.

[JaneLHunt]

Hello @imbarr Thanks for your reply, but unfortunately this is not applicable on my Gateway Server.  This error does not appear in the logs and NT SERVICE\PBIEgwService has access to all the folder areas it needs.  But glad it worked for you!

[JaneLHunt]

Notice a few other people on forum are starting to have data connection issues on Power BI Service with on-premise SQL Server when connectivity was working previously. 

Could it be anything to do with this I noticed on Azure.status.microsoft concerning Azure Active Directory Authentication Issues?

Warning

Azure Active Directory - AAD Authentication Issues

Impact Statement: Starting at 05:25 UTC on 01 Mar 2023, Customers using Azure Active Directory may experience authentication issues when attempting to access Azure, Dynamics 365, and/or Microsoft 365.

Current Status: We are aware of this issue and are actively investigating. An update will be provided within 60 minutes, or as events warrant.

This message was last updated at 08:21 UTC on 01 March 2023

[Dhanak4]

Hi,

Now we also facing the same issue to connect SQL server with windows account. Not sure how to move forword here.

Received error payload from gateway service with ID 177575: Error logging on username 'Domain\Username'

[GilbertQ]

Hi @JaneLHunt 

What happens if you have to change the on-Premise Service account to run using the same Domain\Useraccount and see if that resolves the error?

Other than that I would chat to your DBA to see if anything has changed on this account in the database permissions?