Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JaneLHunt
Helper II
Helper II

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

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

1 ACCEPTED SOLUTION
JaneLHunt
Helper II
Helper II

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.

View solution in original post

21 REPLIES 21

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‌‌!😕

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors