Refresh of Dataflow Gen1 with Gateway Connection to SQL server is failing on Pro workspace

[Iras]

I am using Dataflow Gen1 in the Pro workspace of the Power BI service to connect to an on-premise SQL Server database using On-premise Gateway. A few months ago, everything was working fine. However, for the past couple of months, the refresh has been failing. When I refresh the data from the M-Query of the dataflow it works but when I refresh the dataflow it's failing.

In the error message export from the dataflow nothing is specified. it says ony Error : Request ID :  and Activity ID. 

Here are the steps I have thoroughly checked:

1. No premium transformations or features are used in the dataflow. I even tried using a basic table with 100 rows without any transformations, but it still failed.
2. I checked the Gateway connection configuration. It is working fine, as the same Gateway connection is being used to refresh the dataset, and it works.

3. The on-premises data gateway is up to date.

4. I recreated the dataflow multiple times in different Pro workspaces with different users and still the issue is persists. 

5. I also checked that,  the same dataflow is working perfectly fine when I use the Premium workspace. 

Has anyone experienced a similar issue or have any suggestions?

[Anonymous]

Hi  @Iras,

Based on the above information, this issue is complex which may need to collect log files for further troubleshooting. Since community support engineers don't have that access, I would suggest opening a Support Ticket. If you are a Power BI Pro or Fabric licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you. 
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[Iras]

Hello Community,

To investigate the issue, we enabled logging on both the Gateway and SQL. We found the following error.

However, the same Gateway works fine when the dataflow refresh is triggered from a Premium workspace.

Does anyone know the differences between Dataflow Gen1 in a Pro workspace and Dataflow Gen1 in a Premium workspace regarding connectivity, port usage, or Azure resources?

It will be great if someone can help..

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError
GatewayVersion=3000.246.5
MessageContainsPrivateInformation=True
Reason=DataSource.Error
ReasonContainsPrivateInformation=False
DetailContainsPrivateInformation=True
MessageFormat={0} failed to get the response: '{1}'.
MessageFormatContainsPrivateInformation=False
MessageParameters=[<ccon>AzureBlobs</ccon>;<ccon>Unable to connect to the remote server</ccon>]
MessageParametersContainsPrivateInformation=[True;True] --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<ccon>AzureBlobs failed to get the response: 'Unable to connect to the remote server'.</ccon>

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=MashupValueException
InnerMessage=<ccon>AzureBlobs failed to get the response: 'Unable to connect to the remote server'.</ccon>
InnerToString=<ccon>Microsoft.Data.Mashup.MashupValueException (0x80004005): AzureBlobs failed to get the response: 'Unable to connect to the remote server'.