Error attempting to setup gateway connection for SQL database on local server

[andyor]

Hi

Getting the following error when attempting to setup gateway connection to a SQL server database on a local server. I had no ttrouble when setting up a gateway connection to a CSV file in onedrive on local server. 

I have tried all authentication methods so I believe it's something in SQL stopping remote connections. Stackoverflow gives old solutions about enabling ports under IP addresses, but that option was only in SQL server 2015. Any ideas? 

Failed to update data source credentials: Microsoft SQL: 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

[Anonymous]

Hi @andyor 

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 correct IP
• The TCP port number is not specified correctly

Checklist:

• SQL Server are up are running
• Check the correct instance name
• Add in exception in firewall
• Check if anti virus is using it
• Check the SQL browser services

Please refer the following Microsoft document and cases that could also help you:

https://community.powerbi.com/t5/Desktop/SQL-Connection-error-Named-Pipes/td-p/29203

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-th...

https://support.microsoft.com/en-us/help/4009936/solving-connectivity-errors-to-sql-server

Best Regard

Community Support Team _ Ailsa Tao

[TomMartens]

Hey @andyor ,

what type of gateway are you using:

• on-premises standard installation (aka enterprise gateway), recommended!
• on-premises local installation

Is the gateway installed on a dedicated machine that is close to the SQL server instance, on your local desktop/laptop, or on the same machine? Make sure that you are able to ping the SQL server machine from the gateway machine. This, of course, is mandatory.

Check if the TCP/IP network protocol is enabled for the SQL Server instance you can test this using SQL Server configuration manager tool:

Check if the SQL Server instance allows remote connections, you can check the property of the instance using SQL Server Management Studio:

Hopefully, one of the mentioned aspects helps to tackle your challenge.

Regards,

Tom