topic Re: SQL endpoint connection in Dataflow

SQL endpoint connection

suidino2 — Fri, 09 Jun 2023 03:09:05 GMT

I encounter a problem.

The scenario is as following:

1. Create a Lakehouse

2. Saved several Excel files in File of the created Lakehouse

3. Use Dataflow Gen 2 to load data in those Excel files from 2 load it into Lakehouse Table.

4. Once the table created from 3, we can see table in the Lakehouse SQL endpoint

5. Open PBI desktop and load data from Lakehouse SQL endpoint

Then I encountered following error:

Details: "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)"

Any ideas?

Re: SQL endpoint connection

jwelch — Fri, 02 Jun 2023 15:53:58 GMT

This is likely a firewall restriction on your computer or network. The connection is being made directly from your machine running PBI Desktop to the SQL endpoint in Microsoft Fabric, and may be blocked by your network admins.

You can check this by using SSMS or Azure Data Studio to attempt to connect to the LH endpoint - if it fails, then you would need to talk to your network admins about opening up the firewall for the endpoint. You can get the connection string by selecting "More options" on the SQL endpoint listing in the workspace, and then "Copy SQL connection string".

Re: SQL endpoint connection

miguel — Fri, 02 Jun 2023 17:07:01 GMT

Hey!

What connector are you using when doing this connection test against the Lakehouse?

Re: SQL endpoint connection

suidino2 — Mon, 05 Jun 2023 00:51:12 GMT

Hello, I was using the Datalake connector.

I havent tried it yet, but I think jwelch has the point. The message is kind of same when I tried to connect to Databricks/ Synapse in the past when corporate firewall unmodified.

Re: SQL endpoint connection

PawelWrona — Mon, 26 Feb 2024 13:47:10 GMT

I am not sure how relevant is it sill, but considering that Lakehouse SQL end-point and Dataverse should work the same, I used the steps described as an approach for TDS endpoint in Dataverse article and it worked for me:
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query#blocked-ports