Error with dataflows using On-prem gateway

[PowerUP200]

Hi all, 

I've been trying to set up dataflows which connect to SQL servers using On-Prem gateways. 

I have successfully created a gateway connected to this SQL server (used a semantice model, and it refreshes perfectly on the web service). 

However, when I try to create the dataflow using the same on-prem gateway and refresh it, I consistently get this error: 

Error: AzureBlobs failed to get the response: 'The underlying connection was closed: An unexpected error occurred on a send.'.

Note, this is in a Pro workspace. 

I submitted a ticket to MS, and the interesting thing is when we tested turning the workspace into a PPU workspace, the dataflow started working! I've read the documentation for dataflows and premium features of dataflows, but nothing specifies using on-prem gateways is a premium feature.

Unfortunately, we weren't able to progress the MS ticket as my org forbids sending detailed logs externally, so I'm turning to the community, is anyone's organisation 1. NOT on Premium (i.e., using Pro licences) and 2. Using On-Prem gateways, and have you been able to get dataflows which use on-prem gateways to work? 

Thanks for any info

[v-kaiyue-msft]

Hi @PowerUP200 ,

Encountering the error message "AzureBlobs failed to get response: 'The underlying connection was closed: An unexpected error occurred while sending'" usually indicates a network connectivity issue between the on-premises data gateway and the Azure service. This can be due to a variety of factors, including firewall or network configuration issues.
Here are some links to similar issues to yours, hope they help:Solved: Dataflow refresh Failed AzureBlobs failed to get t... - Microsoft Fabric Community, Solved: Re: Unable to connect - Azure Blob Storage - Microsoft Fabric Community.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerUP200]

Hi @v-kaiyue-msft, 

Thanks for your response, I was wondering if you have more information on "network connectivity issue between the on-premises data gateway and the Azure service"?

What I don't understand is the gateway works perfectly when refreshing a semantic model which connects to the same SQL source. To me, that indicates that the firewall config is letting the data through correctly, so why is the dataflow any different? And why does turning the workspace into PPU magically make it work? Does turning it to PPU change how Azure hits the on-prem gateway when refreshing the data?

Do you have more info on the difference between dataflows and semantic models and how they retrieve data via azure to the on-prem gateway?

[v-kaiyue-msft]

Hi @PowerUP200 ,

1. The Local Data Gateway acts as a bridge that facilitates secure data transfer between local data sources and Azure services, including Power BI. Regarding the difference in behavior between Pro and PPU workspaces, it is worth noting that PPU (Power BI Premium per User) offers additional features and functionality compared to Pro. However, the use of local data gateways is not unique to Pro or PPU as the gateways are designed to work across different license tiers.
2. Consider the refresh duration of the data stream. the timeout thresholds for Power BI Pro data streams are two hours at the individual entity level and three hours at the entire data stream level. PPU is not subject to these timeout limits. For more details refer to the documentation: Understand and optimize dataflows refresh - Power BI | Microsoft Learn.

3. Check the gateway logs. Reviewing the gateway logs may provide insight into connectivity issues. Look for any errors or warnings that occurred during the dataflow refresh attempt.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerUP200]

Hi @v-kaiyue-msft ,

Thank you for your response, very useful to know the difference between PPU and Pro  for gateways.

But could I check, does the gateway work differently between semantic models and dataflows?

[v-kaiyue-msft]

Hi @PowerUP200 ,

The basic purpose of a gateway is to securely connect Power BI to a local data source in both scenarios, with the semantic model typically using a gateway for real-time data connectivity and DirectQuery operations, while data streams primarily use a gateway to bring in data from a local source. The performance impact of gateways can vary, with semantic models potentially facing real-time query performance considerations and data streams potentially encountering limitations related to data refresh times. More information on how gateways work can be found at the link: On-premises data gateway architecture | Microsoft Learn
The Power BI Gateway; All You Need to Know - RADACAD
Using an on-premises data gateway in Power Platform dataflows - Power Query | Microsoft Learn

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.