DATAFLOW and ON-PREM DB connectivity solution

[Element115]

ISSUE:

All sorts of connectivity issues related to using one dataflow gen 2 only to extract data from an on-prem DB via the Power BI data gateway. 

SOLUTION:

Even though in the firewall port 1433 was open for the various Fabric endpoints or wildcard FQDNs

Protocol: TCP
Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com
Port: 1433

as stated per this Microsoft documentation page: On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | ...

it only worked once my sysadmin specified 0.0.0.0 TCP/1433 to all WAN facing destinations (ie no restrictions to particular endpoints or FQDNs), and all DB networking errors go away, as well as all connectivity issues between Fabric dataflows and the on-prem DB.

The only question I have left is this (considering that most people who will read the troubleshooting document are not network admins, but Fabric or business/analyst users, with a few exceptions knowing about networking and firewalls):  Why can't this be mentioned clearly and in bold in the Microsoft Fabric documentation?  And if it is, where is it exactly?  Could have saved us and others and support a lot of grief.

[motoray]

@Element115, I'm not 100% sure I understand what you mean with the statement:

"his case there is already one baked in the firewall--the open TCP/1433 to all traffic rule"

Are you saying that you basically have a firewall rule set up that allows TCP/1433 to anything internally from anything externally? Or are you only allowing anything externally to your gateway server? Or something else?

[Element115]

@motoray Yeah, sorry , that was poorly phrased and I edited my OP to make it more clear (hopefully ;-)).

But in a nutshell:

WAN (internet) Destination == all

Services (ie traffic restriction rules) going from the machine running the gateway out to the internet we do not specify any endpoint (ie wildcard FQDN) as in the MS documentation, rather only 0.0.0.0 for the endpoint over TCP on port 1433.

Which means, any local application generating TCP traffic over port 1433 will be allowed through the firewall and to its destination on the internet.  

As to the converse, ie any TCP traffic over 1433 in the direction of the server running the data gateway on the LAN will have to pass through the Microsoft data gateway first. My sysadmin explained that the gateway filters all traffic over 1433 and thus takes care of accepting only legitimate 1433 traffic from Microsoft before relaying it to the DB server.  This makes me wonder about the potential security implications, what about SQL injections and the like...?

[motoray]

Thanks--that clears it up.

[Element115]

.

[Joshrodgers123]

We went through this craziness today. 

We found that you also need to allow "*.pbidedicated.windows.net", not just "*.datawarehouse.pbidedicated.windows.net"

[v-nikhilan-msft]

Hi @Element115 
I have updated the internal team regarding this issue. They are working on it.
Appreciate your patience.