Forum Discussion
Error loading data from on prem SQL server to Fabric Lakehouse with Dataflows Gen2
The solution was in our case that is was not enough to open up port 1433 en whitelist the right addresses *.datawarehouse.pbidedicated.windows.net and the more usecase specific *.northeurope.cloudapp.azure.com in the Fortinet firewall but to open up SQL access in the firewall from our VM (where the gateway is) to the Fortinet maintained internet service 'Azure'.
Our gateway is on a VM, so if that VM should be able to connect to the SQL Endpoint, right?
Not necessarily. What needs to be done is that your team will need to add the endpoint of the Lakehouse (either the DNS / servername to it or the direct ip that resolves from that servername) to your allow list for firewall reasons in order for the Gateway to be able to communicate directly to the Lakehouse.
What's currently being blocked is that when your gateway is trying to connect to the Lakehouse to load the data, it is not able to do so given the firewall rules set at the VM where your gateway is located.
I'm having the same error. What is the reasoning behind this arquitecture? if gateway is set to enable the data in the cloud, and the workflow/pipeline is running in the cloud, why this error when dataflow/pipeline runs?
I understand the SQL endpoint must be secured from external connections, but I didn't expect this issue if I'm already bringing data to cloud through the gateway. It also doesn't make sense we don't get any error when building the dataflow.
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.
I don't understand what the problem is in using the Azure Synapse approach since the capacity is owned by the user.
Thank you
Anonymous wrote:if gateway is set to enable the data in the cloud, and the workflow/pipeline is running in the cloud, why this error when dataflow/pipeline runs?
The gateway still needs to send the data from your network to the cloud. For performance reasons, it's better if the Gateway can send the data directly to the destination in the cloud, rather than introducing multiple hops and having to "land" the data in temporary locations.
It also doesn't make sense we don't get any error when building the dataflow.You don't see an error because when you are building the dataflow, the requests to the destination are not routed through the Gateway - that only happens during the Refresh operation. That is also a performance operation. However, we are looking into ways to validate the connectivity so that authors can be aware of this earlier.
-John
thanks jwelch
Is it possible to attach endpoints in Fabric to virtual network in Azure ?
Can you share documentation on how to do it or how to enable connectivity from gateway to endpoints?
