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

[marcoG]

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. .

[bcdobbs · Accepted solution]

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

[marcoG · Accepted solution]

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.

[bcdobbs]

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

[bcdobbs · Accepted solution]

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

[Haroonzuberi1]

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

[charrington]

@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!

[bcdobbs]

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

[charrington]

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

[marcoG]

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

[miguel]

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

[bcdobbs]

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?

[marcoG]

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