Forum Discussion
Getting On-Prem data from SQL Server to Microsoft Fabric using Dataflow (gen2)
Dhruv_Ranpura so guys, ok so here's the deal (I am learning a lot about all this firewall stuff, hence why all the back and forth).
port 1434 is irrelevant.
One more thing for future readers: The endpoint listed on the MS doc page (*.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com) are wildcard FQDNs. So it depends on the firewall what you can or cannot setup from what I've been told.
In our case, we specified the 0.0.0.0/TCP/1433 for outgoing traffic and that did the trick.
Oh man, what confusion... finally we got to an understanding. 😥 The Microsoft documentation should seriously be rewritten on this point, because the people who are going to read it are Fabric users, not network admins.
That's not it. I had the same exact issue for days. Everything was working fine before. And when it was working fine, my gateway had already been configured according to the Microsoft doc. As a matter of fact, the issue still happens today when using only ONE DFg2. So that cannot be the answer.
However, if Dhruv_Ranpura you use 2 chained DFg2, that is, one extracts the data from the on-prem DB, and the second one uses the first DF as a source to load the data into the lakehouse, then this works. The chaining is setup as follows:
DF1 needs staging enabled AND no data destination AND you need to publish it
DF2 needs staging disabled AND a data destination, ie your lakehouse AND you need to publish it. (I say 'needs staging disabled' because if enabled you would duplicate your data and use twice the Fabric storage, that is, once when the data is copied into the default staging lakehouse automatically provisioned by Fabric, and twice when storing the data into your user created lakehouse.)
v-cboorla-msft Here is the case# 2401190040000098 I have with support for this goddamn issue that hasn't been fixed in months now. I guess we must hope that the release of the new OPGW (on-prem gateway) will fix all this and allow on-prem data extraction to be performed with one DFg2 instead of 2.
Thanks Element115 for your response.
I have been trying the method suggested by you. I have created one Dataflow(Gen2) with no data destination selection and published it. Now, I created another Dataflow(Gen2) and choose source as Dataflows (Fabric) and selected Dataflow1 and its table and when click on next I am getting this error.
I have checked that my SQL server is working fine on port 1433. So, why I am getting this error? Please guide.