Problem to import on premise data onto a lakehouse using a DataflowGen2

[alfBI]

Hi,

I am trying to load data from an on premise SQL Server onto a Fabric lakehouse using a Dataflow Gen2.

We have alreday in place a on-premise gateway with the most updated release (November 2023)

First strange thing occurs when creating the connection to the dataflow gen2 

After clicking here

I fill the the info to connect to the SQL Sever. I select the table to be imported and when trying to select the lakehouse as a destination process get stuck

after clicking next process of connection validation keeps runnning forever

until it ends with following error message

An exception occurred: Downstream service call to url 'https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails' failed.

any idea about what is going wrong here?

Many Thx

Alfons

[v-cboorla-msft]

Hi @alfBI 

Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

Thanks

[alfBI]

We upgraded to latest gateway release (3000.198.17) and problem remained. Problem was fixed removing the proxy and reviewing the outpbound request. We noticed that por 1433 was not not allowed for *.datawarehouse.pbidedicated.windows.net. Once added everything worked finally

Anyway I have the feeling that documentation about the list of FDQN used on the gateway

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-communication

should be improved as we have to add additional URLs to the proxy white list

* .analysis.windows.net

*. applicationinsights.azure.com

*.data.microsoft.com

*.dfs.fabric.microsoft.com

Thx for your support

[dazhang]

Hi @alfBI, thanks for the details, sorry for the delay.

For the endpoints you listed:

• *.analysis.windows.net - this is already included in the documentation
• *.applicationinsights.azure.com - our documentation includes *.dc.services.visualstudio.com which is for Application Insights, did you already try adding this?
• *.data.microsoft.com - can you give more details on where you see this is needed?
• *.dfs.fabric.microsoft.com - we just added this to the documentation, thanks for calling it out.

Thanks,

David

[daamruth]

Hi there, 

we had the same problem and were only able to resolve it by not only allowing *.datawarehouse.pbidedicated.windows.net:1433 on the firewall but more (random?!) IPs.

After allowing *.datawarehouse.pbidedicated.windows.net:1433 was not enough, we opened *:1433 and found that the following IP adresses were also adresseed during a load: 

20.38.86.50
51.107.48.217
51.107.48.222

They are not able to be assigned to a FQDN. 

After doing so we are now able to load data from onPrem, via StagingLakehouse into the final lakehouse. 

@v-cboorla-msft : What are these IPs? Please update the documentation since only allowing *.datawarehouse.pbidedicated.windows.net:1433 is definetly not enough. 

Thanks

Daniel 

[alfBI]

Hi,

Effectively there was a problem on the config file "Microsoft.Mashup.Container.NetFX45.exe" never noticed before until now (we have started to use the gateway to load dataflows).

Once fixed we are finally to connect lakehouse to destination, (but with lot of patience, the message below "Validating Connection" appeared during more than 2 minutes....

once completed, navigation across workspaces to reach the lakehouse was extremely slow and once the lakehouse was selected it took a long time to create the table (> 2 min).

So, more than 5 min only to have the dataflow ready......Finally despite the fact that the dataflow was published following error does appear when executed

What are we doing wrong here? Access to  *.datawarehouse.pbidedicated.windows.net is already enable on our firewall...... Sounds we are each time closer but not enough 🙂

Many Thx

[dazhang]

Hi Alfons,

"The value is not updateable" is an issue that we just published a fix for today, would you mind upgrading to the latest version of the on-premises data gateway (3000.198.17) and try refreshing your dataflow again?

Thanks,

David

[v-cboorla-msft · Accepted solution]

Hi @alfBI 

Thanks for using Microsoft Fabric Community.

As I understand that you are trying to load data from an on premise SQL Server onto a Fabric lakehouse using a Dataflow Gen2.

In scenarios where your data sources are located on-premises, Microsoft provides the On-Premises Data Gateway to securely bridge the gap between your on-premises environment and the cloud. This document will guide you through the process of accessing on-premises data sources within Data Factory for Microsoft Fabric using the On-Premises Data Gateway.

For more details please refer to the below mentioned links:

Install an on-premises data gateway.

How to access on-premises data sources in Data Factory for Microsoft Fabric.

On-premises data gateway considerations for data destinations in Dataflow Gen2.

I hope this information is helpful. Please do let us know if you have any queries.

[dazhang]

Hi Alfons,

This error message suggests that the dataflow is having trouble communicating with the Fabric endpoint via the on-premises data gateway. Is your on-premises data gateway machine behind a firewall, and, if so, can you check your firewall logs to see if there was a failed connection attempt made to https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails?

Thanks,

David