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'.
thanks for your guidance jwelch I tried the SQL connection string of the destination lakehouse in SSMS and indeed it gave the same sort error:
------------------------------
ADDITIONAL INFORMATION:
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) (Microsoft SQL Server, Error: 53)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error
------------------------------
The network path was not found
----------------------------------------------------------
So now I have to ask the VM / network IT team to allow for connection to this lakehouse and from here on every time we want to use or test a new lakehouse repeat that proces or is there a way to use a range of IP's or something like @miguel suggested?
Our gateway is on a VM, so if that VM should be able to connect to the SQL Endpoint, right?
I looked a the list of IP's for Azure, but there are a lot of adresses 🙂 I would love some help on which to use here.
Download Azure IP Ranges and Service Tags – Public Cloud from Official Microsoft Download Center
Our capacities are in North Europe.
Maybe PowerQueryOnline, DataFactory, SQL, AzureDataLake?
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.