Cannot add new data source to gateway

[Markzolotoy]

The client has added a new SQL server and also installed new Gateway.I can see it on the powerbi service page by going to manage Gateways. Adding new data source is not working. I keep getting the following error:

The on-premises data gateway's service account failed to impersonate the user. I am using Windows authentication. Using Basic will trigger this error:

QL Server connection failed
Underlying error code:	-2146232060
Underlying error message:	A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)
DM_ErrorDetailNameCode_UnderlyingHResult:	-2146232060

Any idea where to troubleshoot? The gateway has been reinstalled a couple of times. Didn't help.

Thanks

[Markzolotoy]

The second error message is about the SQL connection which occurs if I try Basic authentication. But with Windows authentication is not even mentioning the SQL connection:

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

[GilbertQ]

Hi @Markzolotoy 

Can you confirm that you can connect via SSMS to the SQL Server on the Gateway server?

[Markzolotoy]

Ok, what then did you mean that Gateway cannot connect to SQL Server? They are installed on the same box.

[GilbertQ]

Hi @Markzolotoy 

According to the error it does not look like it can connect. How are you connecting to the SQL Server, put those some details in the Gateway source in the Power BI service?

[Markzolotoy]

But what about the first message:

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

Also using that windows accoutn I can login to SQL Server.

[GilbertQ · Accepted solution]

As far as I know you first need to be able to connect to the SQL Server database before it can authenticate?

[Markzolotoy]

It was a wrong password :>(

[GilbertQ]

Awesome news, glad to hear you got it working!

[GilbertQ]

Hi @Markzolotoy 

The issue is that your Gateway server cannot connect to your SQL Server.

If you can log into the Gateway server and from there connect to the SQL Server. Then use those some connection details (Server address) and user name and password are put into the Gateway when setting it up in the Power BI Service.