PowerBI OnPRem Gateway is NOT connecting to Secured Network but working on Public IP

[dk_power]

Dear All,

I have my PowerBI Online version, the OnPremise Gateway is setup in the SQL Server Machine.

However when I tried to add the datasource under the gateway in "Manage Gateway" Option, its throwing error.

One thing we have noted is, if we keep the SQL server machine OPEN to PUBLIC then "its working good", but we cannot afford to keep it open for security reasons. Also we have tried whitelising some of the IPs from "GatewayPortsTestResults2020-05-13T22_11_56" file, even then there is NO luck. 

[v-yingjl · Accepted solution]

Hi @dk_power ,

This error usually means that the client can't find the SQL Server instance. This normally happens when at least one of the following problems exist:

• The name of the computer hosting the SQL Server
• Instance does not resolve the the correct IP
• The TCP port number is not specified correctly

Please follow the instructions in the article below to troubleshoot this connection error: 

Troubleshoot connecting to the SQL Server Database Engine 

You can also refer this simliart issue: Re: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server 

Best Regards,
Yingjie Li

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

[dk_power · Accepted solution]

Finally we have Whitelisted the Public IP of the AWS EC2 instance - the same machine where both SQL Server and Gateway is installed. Now Gateway is working as expected. Its a kind of Circular Reference.

[v-yingjl]

Hi @dk_power ,

When using on-premiss gateway to connect to SQL Server, please try to set the privacy settings for this datasource as Organizational or Private to connect again.

Best Regards,
Yingjie Li

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

[dk_power]

Hi Yingjie, Thanks for your response, I have tried both Org and Private. No luck as of now.

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)

[v-yingjl · Accepted solution]

Hi @dk_power ,

This error usually means that the client can't find the SQL Server instance. This normally happens when at least one of the following problems exist:

• The name of the computer hosting the SQL Server
• Instance does not resolve the the correct IP
• The TCP port number is not specified correctly

Please follow the instructions in the article below to troubleshoot this connection error: 

Troubleshoot connecting to the SQL Server Database Engine 

You can also refer this simliart issue: Re: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server 

Best Regards,
Yingjie Li

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

[dk_power]

Hi Yingjie, Our Situation is not like "Its completely NOT working". Gateway is working if we keep the SQL Server machine open to public, but if we limit to only whitelisted ips its not woring. Based on the directions from MS support team we have whitelisted all 132 IPs from the uswest Region Azure Datacenter, still there is no luck.  Please let me know if there is any resolution for this specific case.

Thank you for your consistent postings.