Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
alfBI
Resolver I
Resolver I

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

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

 

alfBI_0-1699623777853.png

 

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

 

alfBI_1-1699623928922.png

 

 

alfBI_2-1699623941512.png

 

after clicking next process of connection validation keeps runnning forever

 

alfBI_3-1699623979842.png

until it ends with following error message

 

alfBI_4-1699624008865.png

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

 

 

 

 

1 ACCEPTED SOLUTION
v-cboorla-msft
Community Support
Community Support

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.

View solution in original post

8 REPLIES 8
v-cboorla-msft
Community Support
Community Support

Hi @alfBI 

 

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

 

Thanks

alfBI
Resolver I
Resolver I

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
Microsoft Employee
Microsoft Employee

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
Helper II
Helper II

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
Resolver I
Resolver I

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....

 

alfBI_1-1699815901423.png

 

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).

 

alfBI_2-1699815971813.png

 

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

 

alfBI_3-1699816262098.png

 

alfBI_4-1699816316123.png

 

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
Microsoft Employee
Microsoft Employee

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
Community Support
Community Support

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
Microsoft Employee
Microsoft Employee

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

November Update

Fabric Monthly Update - November 2024

Check out the November 2024 Fabric update to learn about new features.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.