On-premise SQL connection doesn't work on Gateway from PBI Service (does it need encryption local?)

[troyel]

Hi,
I am trying to connect to On-Premises data gateway and onto a SQL  server on the local network of the gateway. 

I have tested and confirmed that:
1. The gateway can successfully connect and refresh a local flat file (excel) on the gateway through the service.
2. The PBI Desktop on the gateway can connect and load data from the SQL server on the local network using SQL authentication.

However I can't get the PBI Service to connect to the SQL server through the data gateway. I get the following error message:

Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Version:	13.0.4759.155

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

DM_ErrorDetailNameCode_UnderlyingNativeErrorCode:	87

I am using the exact same credentials that I have on the gateway for PBI Desktop to connect. One thing I am thinking about is that when connecting from the PBI Desktop I am running an "unencrypted" connection as SSL /sertificate is not set up between the gatway and the sql server. But this works well on the desktop. 

Any ideas or suggestions? I would assume if it was a certificate issue there would be a more specific error message.

Thanks!

[Seth_C_Bauer · Accepted solution]

@troyel My first thought here is always that something in the connection string is wrong. Looking up the tail end of the error I would re-check that. Possible simliarities correlate with this post about that error in an application.

https://www.sqlservercentral.com/Forums/Topic1816362-2799-1.aspx

[troyel]

It turns out that when connecting to a specific port in the Power BI gateway from PBI Service you need to specify the port number as ip , port e.g. 10.0.0.100,1234 and not :1234 as I do in PBI Desktop. This would be great to have in the documentation or at least have it harmonized between the two. Thanks for putting me on the right track @Seth_C_Bauer!

[troyel]

Hi @Seth_C_Bauer,

Thanks for the reply. The connection string is just the local network IP adress e.g. 10.xxx.xx.xx and the port number so:

10.xx.xxx.xx:62123 - which is exactly the same as I use to connect the desktop to the same sql server.

Could it be that this issue is port-related? I also tried without specifying the port number, stil didn't work.

Thanks

[troyel]

I have done some testing using a similar environment and it seems that the connection from gateway fails when I try specifying the port number behind the ip adress. It works on general 1434 but not when the port is specified. Anyone else encountered this issue or managed to solve this in a similar context?

Thanks!