Data pipeline with parameterized on-prem db connection and data store type external staging account

[TGTun]

Hi Folks,

We are trying to use parameterized on-prem db connection to copy data into Fabric Warehouse in data pipeline. To copy data from on-prem to cloud, we have to use external staging account as blob storage. We did "test connection" for each connectivity - source, destination and stageing account. All are connected successfully and we are able to preview data after successful connection. Got issue when save and run.  Is anyone else experiencing this issue?

1. Before saving, Failed validation with below message. 

Copydata_OnpremToCDWH

To copy data with an on-premises or a VNet data gateway, ensure the source connection enables 'Allow this connection to be utilized with either on-premises data gateways or VNet data gateways'. Please edit the connection to enable this option.

2. When we tried edit staging account connection, it's not able to enable "Allow this connection to be utilized either on-prem or VNet data gateway"

[v-hashadapu]

Hi @TGTun , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @TGTun , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @TGTun , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[burakkaragoz]

Hi 

I'll help troubleshoot this issue with connecting an on-prem database to Fabric Warehouse using an external staging account. Let me analyze the error and provide a solution.

Based on the screenshots and description, I can see you're encountering two main issues:

1. A validation error when trying to save your data pipeline configuration
2. An inability to enable the "Allow this connection to be utilized with either on-premises or VNet data gateways" option for your staging account

This appears to be a common configuration challenge when setting up hybrid data movement scenarios in Microsoft Fabric.

Solution:

The error message indicates that you need to properly configure the connectivity between your on-premises data source and your cloud destination. Here's how to resolve this:

1. For your source connection (on-prem database):
   • Edit the connection properties
   • Enable the "Allow this connection to be utilized with either on-premises data gateways or VNet data gateways" option
   • This setting must be explicitly enabled for connections that will utilize a data gateway
2. For your staging account (Azure Storage):
   • It appears the checkbox is grayed out in your staging account connection
   • This is likely because the storage account type or authentication method doesn't support direct gateway connectivity
   • For blob storage used as staging, you should:
     • Use account key authentication rather than SAS
     • Ensure the storage account allows access from trusted Microsoft services
     • Configure appropriate firewall settings on the storage account
3. Data gateway configuration:
   • Verify your data gateway is properly installed and registered
   • Check that the gateway service is running with an account that has appropriate network access
   • Ensure the gateway has the latest updates
4. Pipeline configuration:
   • When configuring the copy activity, explicitly specify the integration runtime that uses your gateway
   • Set the staging settings to use your blob storage account

Here's what the corrected configuration approach would look like:

1. Create a self-hosted integration runtime and register your gateway
2. Configure your source connection with gateway access enabled
3. Configure your destination (Fabric Warehouse) connection
4. Configure your Azure Storage connection for staging
5. In your copy activity, specify the integration runtime and staging settings

If the checkbox remains disabled for your staging account, you may need to create a new connection using the account key authentication method instead of the current configuration.

[TGTun]

To add-on, there is no issue if we are using direct connection for source, destination and stage account connection that can be validated ok, saved and able to run. Data is copied over to destination. It's only happened when we are using parameterized on source connection. 

Thanks in advance.

Theingi

[v-hashadapu]

Hi @TGTun , Thank you for reaching out to the Microsoft Community Forum.

The issue arises because when using a parameterized on-premises database connection in Ms Fabric, all linked connections, including the Azure Blob Storage staging account must also be compatible with the same on-premises or VNet data gateway. The validation error occurs because the Blob connection wasn't originally created with gateway support and the required option (“Allow this connection to be utilized with either on-premises or VNet data gateways”) can only be enabled at creation time, not afterward. This limitation causes the pipeline to fail validation when parameters are used.

To fix this, delete and recreate the Azure Blob Storage connection in Data -> Manage Connections and Gateways. During setup, input your account details and enable the gateway checkbox. Make sure to select the correct on-premises or VNet gateway. This ensures full compatibility during pipeline execution. Instead of parameterizing the entire connection, keep the connection static and only parameterize elements like file paths, container names or folder names within datasets or activities. This avoids validation issues and keeps your pipeline dynamic and maintainable.

If the issue persists, consider using two pipelines: one to copy from the on-prem source to Blob Storage using the gateway and a second to copy from Blob to the Fabric Warehouse. This separation simplifies gateway handling and avoids parameterization conflicts. Finally, verify that your gateway can access Blob Storage. Check that it’s running, correctly configured and not blocked by firewall or VNet rules. Tools like Test-NetConnection can help validate network access from the gateway machine.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[TGTun]

Hi @v-hashadapu, 

Thanks for your response. 

We tried create with Cloud gateway connection that is with the option of "Allow this connection to be utilized with either on-prem data gateways or VNet data gateways". Got error as below screenshot and detailed message also. We are able to create with on-prem gateway with no issue and that one is not available for the option to enable. 

Unable to create connection for the following reason: Invalid connection credentials.

Details:

The credentials provided for the AzureBlobs source are invalid. (Source at https://xxxxxxxxx.blob.core.windows.net/.)

Please have this information handy if you choose to create a support ticket.

Session Id: 2bb7dbe4-0ad7-4a52-ab53-3623f155f132
RequestId: 4f41034e-0b3c-4468-9b04-ddada5e8c198
Cluster URI: https://api.powerbi.com
Status code: 400
Time: Thu Apr 24 2025 22:07:42 GMT+0800 (Singapore Standard Time)

[v-hashadapu · Accepted solution]

Hi @TGTun , Thank you for reaching out to the Microsoft Community Forum.

You're encountering this issue due to a current limitation in Microsoft Fabric, when using a parameterized on-premises source connection via a gateway, any linked connections like your Azure Blob Storage staging account must also support gateway usage. However, Fabric does not fully support credential validation through the gateway for Blob Storage when used in parameterized pipelines. This causes the “Invalid connection credentials” error even when credentials are correct, as confirmed by the successful direct connection.

The most reliable workaround is to split your pipeline into two stages. In the first, copy data from the on-premises source to Blob Storage using a non-parameterized pipeline with the gateway. In the second, copy data from Blob to the Fabric Warehouse, this cloud-to-cloud step requires no gateway and supports parameterization. This structure avoids the unsupported gateway + parameterization combination.

Optionally, try switching your Blob Storage connection from an account key to a Shared Access Signature (SAS) token. While it won’t bypass the gateway limitation, it may improve compatibility or reduce future connection issues in complex pipelines.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[TGTun]

Thanks for confirmation that it's current limitation. So, We will not use parameterized on-prem source connection via gateway. We will go with direct gateway connection. 

Appreciate for your suggestions as well. Thank you.