Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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.
Hi @alfBI
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.
Thanks
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
Hi @alfBI, thanks for the details, sorry for the delay.
For the endpoints you listed:
Thanks,
David
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
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
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
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
Check out the November 2024 Fabric update to learn about new features.
User | Count |
---|---|
5 | |
5 | |
3 | |
3 | |
2 |