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.
Basically, 0.0.0.0 means any domain.
The main thing is this: outgoing TCP traffic on port 1433 needs to be open. This port is used by MS SQL Server, so implicitly you are telling the firewall let all outgoing MS SQL Server traffic through.
As to the Microsoft wildcard FQDNs, if your IT dept tells you it's a no-go, then I don't know what to tell you. Either you open outgoing traffic on port 1433 to any domain, or to the wildcard FQDNs provided by Microsoft in their documentation. Since Microsoft does not provide non-wildcard FQDNs, you can't specify anything else but these 2 categories of domains I just mentioned.
Our system admin has opened th end points (see details on the buttom), but I still got an error when trying to add a Lakehouse as my destination. Our admin checked the firewall and found traffic to destination port 1433 was blocked because the traffic is not encrypted. What does port 1433 stand for? the sql end point used by Fabric to import data? I have created a sql-server connection in on-prem gateway, our system admin has opened the sql end points per MS documentation, why the traffic is still considered as not encrypted?
end points already opened by our itadmin:
Protocol: TCP
Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com
Port: 1433
*.datawarehouse.pbidedicated.windows.net
*.datawarehouse.fabric.microsoft.com
*.dfs.fabric.microsoft.com
*.analysis.windows.net
*.data.microsoft.com
Port 1433 is traditionally the port used by MS SQL Server.
About encryption, I think there's a switch somewhere that you can toggle on/off. Don't remember now if it's in the gateway GUI or SQL Server.