topic Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error in Dataflow

Dataflow Gen 2 from on premise(Sql server) to lakehouse error

marcoG — Thu, 08 Jun 2023 09:39:39 GMT

I have yet update gateway to last release 3000.174.13 and i had set proxy in the gateway to permit cloud connection.

Is there some functionalities to monitor and admin dataflow gen 2 connection?

Thanks!

Error type:

WriteToDatabaseTableFrom_TransformForOutputToDatabaseTableFrom_StatoSinistro

Error description:

null Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.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) Details: DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = 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);ErrorCode = -2146232060;Number = 53;Class = 20 GatewayObjectId: 305f0d41-e624-4c13-b947-0f2761403cb7. .

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

miguel — Fri, 09 Jun 2023 19:38:30 GMT

This might be a situation where your firewall might be preventing a connection to write data to the staging location. You could monitor your firewall to see if this is the case and if there some connection attempts being blocked by it

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

marcoG — Sat, 10 Jun 2023 10:36:59 GMT

With Azure Synapse integration runtime don't have firewall problem...
Can Microsoft Fabric, from on premise sql to lakehouse, also achieve the same result through corporate proxies?
In Azure Synapse each source and each target can decide which integration runtime to use.
Therefore, using the gateway for source and destination, a direct connection is used, while using the gateway for the on premise source and without the gateway for the cloud source, network problems would be avoided.
The solution of opening endpoints on the firewall for each lakehouse is impractical.
Why don't use Azure Synapse approach since the capacity is owned by the user?
Thank you

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

bcdobbs — Sat, 10 Jun 2023 17:36:06 GMT

I'm getting the same. What needs to be open on the firewall to allow connectivity. Never had a firewall issue with dataflows gen1 so assume it's something required to be open to allow acces to one lake storage?

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

bcdobbs — Sun, 11 Jun 2023 18:32:50 GMT

I think I'm starting to understand the problem. A workspace has a single SQL endpoint that covers all of the lakehouss/warehouses etc. I can connect to it outside of workplace with SSMS but inside SSMS gives the same connection issue.
I've asked IT to ensure *.datawarehouse.pbidedicated.windows.net isn't being blocked and can access port 1433 (Connectivity to data warehousing - Microsoft Fabric | Microsoft Learn).

Will update tomorrow.

Ben

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

bcdobbs — Mon, 12 Jun 2023 10:47:47 GMT

Yes, that fixed it.

We've added rules to allow *.datawarehouse.pbidedicated.windows.net to communicate on 1433.

Not sure if it was needed but we've also added rules for *.uksouth.cloudapp.azure.com as there seemed to be some redirects going on.

Ben

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

marcoG — Tue, 13 Jun 2023 07:37:28 GMT

Thanks for the solution found, but this for me is not the best solution but a workaround.

The solution must use the corporate proxy and must not include firewall openings

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

charrington — Tue, 20 Jun 2023 17:58:25 GMT

bcdobbs Hi thanks for the info.
I'd like to tell my infra guy where exactly to add that rule in Azure, do you have more details?
(sorry I know it's basic question, but I am not Azure admin and the admin asks what he needs to do exactly)
Thanks!

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

bcdobbs — Wed, 21 Jun 2023 17:58:59 GMT

I was talking about on prem firewall not azure (unless all your servers are VMs in azure). Afraid I can't help.

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

charrington — Thu, 22 Jun 2023 14:05:22 GMT

That's ok thanks anyway, we were able to figure it out. But you are right, it was on prem firewall.

Have a good day

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

Anonymous — Mon, 17 Jul 2023 20:32:26 GMT

Hello, I am facing the exact same issue. Can you please explain the process of this step "We've added rules to allow *.datawarehouse.pbidedicated.windows.net to communicate on 1433."? So far I have taken the following steps:
1) I went into my "Windows firewall with advanced security" in the machine that is hosting the Gateway and added Inbound & Outbound rules to allow Port 1433.
2) Restarted the Gateway and tried to run Dataflow Gen 2 to Lakehouse again and still getting the same error.
Not sure where I am supposed to add the Endpoint Step "datawarehouse.pbidedicated.windows.net to communicate on 1433". Please help

Re: Dataflow Gen 2 from on premise(Sql server) to lakehouse error

marcoG — Wed, 19 Jul 2023 07:48:40 GMT

I found this article:

https://learn.microsoft.com/en-us/fabric/data-factory/gateway-considerations-output-destinations

And this fabric blog article:

https://blog.fabric.microsoft.com/it-it/blog/data-factory-spotlight-dataflows-gen2?ft=All:

So i will attend for next release to disable staging area so I don’t need to use 1433 port.

I think this will work for me.