On-premise data gateway failed to impersonate

[Anonymous]

Dear community members,

I am running a test environment where I have installed the On-premise gateway on my laptop and then trying to add the SQL server (installed on the same machine) to the connection, but I am getting the following error: 

I have tried all the methods of authentication: Basic, Windows (above error), and OAuth2 (below):

Please advise.

Thanks,

vir

[Anonymous · Accepted solution]

I had faced similar issue (impersonation) while connecting to SSAS Tabular model on the same system. It got resolved by creating another SQL login and then using that login credentials to configure and connect the on-premise gateway. And it worked by using Basic authentication method.

Thank you all for your time and support.

Vir

[Anonymous]

Hello All,

Please advise on the issue. It would be of great help!

Thanks,

vir

[V-lianl-msft]

Please update on-premise gateway to the lastest version.

https://community.powerbi.com/t5/Desktop/Update-to-new-gateway/td-p/818241 

If you already have updated the gateway to the latest version, then you can refer to steps below for troubleshooting.

1. Check if the server is using a proxy and the proxy. Some proxies restrict traffic to only ports 80 and 443. By default, communication to Azure Service Bus will occur on ports other than 443.

2. You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. You will need to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. Change the value from AutoDetect to Https. This file is located, by default, at C:\Program Files\On-premises data gateway.

3. Restart the gateway and check if the issue persists. 

• To stop the service, run this command:

  ''' net stop PBIEgwService '''

• To start the service, run this command:

  ''' net start PBIEgwService '''

4. You may export the data gateway log by selecting Diagnostics and then selecting the Export logs link near the bottom of the page. Reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#tools-for-troubleshooting.

[Anonymous]

Hello @V-lianl-msft 

Thank you for your response.

I am running the latest version of On-Premise gateway as I installed it a couple of days ago.

Also, the SQL server is not using any proxy.

The gateway setting is on HTTPS mode, if that is what you are talking about:

If not, then how do I configure the dll.config file?

Thanks,

Vivek

[V-lianl-msft]

Your problem is probably related to you attempting to connect to an instance called "MSSQLSERVER". Yes this instance name does look like the name of the default instance (e.g. when you look in Services) but when you specify an instance name, SQL tries to find someone with that name which really doesn't exist.

In your connection string that worked, you specify the port number (which happens to be the port number of the default instance) along with an instance name (MSSQLSERVER). The instance name is NOT used when connecting because you specified the port number.

The issue is because when a port number is specified, there is no need to ask SQL Browser for the port number the instance is using ... no need for this because you already have it. If you do not have a port number, and you are not connecting to the default instance (i.e,. you did not specify the name of an instance) then you must ask SQL Browser for the port number.

You can also refer to this thread:

https://community.powerbi.com/t5/Service/Data-Gateway-error/m-p/943147