Power bi gateway error sql server name

[Anonymous]

In power bi desktop i m connecting with the full server name as 12.123.1.12. When I use this server name in the service in order to connect the gateway, it will return:

"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I have tried adding port number as well as using localhost. The latter works but this differs from the server name used in the power bi desktop so it wont connect the report to the gateway when published. 

Any help would be highly appreciated. 

[lbendlin]

Change your Desktop to use the FQDN as well. It is generally considered bad practice to use IP addresses.

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to use server name like: <computer_name>\<instance_name>? Please confirm if the TCP IP is enabled and sql server browser service is running in SSCM.

The link between your dataset and the data source within the gateway is based on your server name and database name. These names must match. For example, if you supply an IP address for the server name within Power BI Desktop, you must use the IP address for the data source within the gateway configuration. If you use SERVER\INSTANCE in Power BI Desktop, you must use it within the data source configured for the gateway.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thanks @v-deddai1-msft  @lbendlin  for the reply. 

The TCP IP is enabled and sql server browser service is running in SSCM.

It is correct that the names should be the same, but this would not work because the name of the server would not be recognized without being in the internal network or being connected to a vpn.

We have used a DNS to forward the incoming IP to the localhost in order to make it work. Alternatively we could have used a VPN to connect to the cloud server but this seemed like a faster solution. 

However, right now, the GW just uses extreme CPU resources and does not perform the refresh at all... 

[v-deddai1-msft]

Hi @Anonymous ,

You may need to configure proxy settings for the on-premises data gateway, please refer to https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

Compare the connection string on the Power Query definition to the connection string reported by the gateway. They need to match exactly (this is even case sensitive now!).

Named pipes sounds weird.  Are you sure your SQL server has TCP enabled?

[Anonymous]

@lbendlin When I try to set up data sources in my gateway, I can only connect using server name (text). However, my Power BI desktop connects to SQL server only using the server address (numeric). How can I resolve this?