topic How to connect external sql server in dataflow? in Power Query

How to connect external sql server in dataflow?

Syndicate_Admin — Thu, 09 Mar 2023 07:21:41 GMT

I would like to make a dataflow which retrieve data from external (No Azure) sql server and create records in dataverse. But I cannot connect the sql server and got error "An exception occurred: DataSource.Error: 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)"

How I tried is as follow:

Re: How to connect external sql server in dataflow?

v-jingzhang — Tue, 14 Mar 2023 06:43:36 GMT

Where is the SQL Server database located? If it is on an on-premises server/computer or in a VM, you need to configure an on-premises data gateway to query data from the database. An on-premises data gateway is used to connect your cloud services to on-premises data sources.

What is an on-premises data gateway? - Power Apps | Microsoft Learn

Manage an on-premises data gateway in Power Apps - Power Apps | Microsoft Learn

In addition, the error message indicates that you cannot connect to the database server. You can use PowerShell Test-NetConnection command to test whether the server and port can be connected from your computer successfully. For example,

Test-NetConnection -ComputerName xx.xx.xx.xx -Port xx

If you cannot connect to the server and port, you could refer to Lesson 2: Connecting from Another Computer - SQL Server | Microsoft Learn to check whether the database server allows the remote connection.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Re: How to connect external sql server in dataflow?

Syndicate_Admin — Mon, 03 Apr 2023 05:11:59 GMT

Hi @Syndicate_Admin

I have tested as you said. It replies success.

TcpTestSucceeded : True