Linked Service to on prem SQL server works everywhere except in data flow

Question

Linked Service to on prem SQL server works everywhere except in data flow

Brian Daddino 0

I have a lined service to an on prem sql server through a VPN. Test connection works. The linked service works through pipelines as well. However when trying to use it in a Dataflow I get the following error.

Only one valid authentication should be used for XXX. SQLAuthentication is invalid. One of user/password is missing

I have confirmed username and password are entered. Again, it works everywhere except in a data flow.

Smaran Thoomu 23,095 Reputation points Microsoft External Staff Moderator

2025-05-06T12:43:52.3233333+00:00

@Brian Daddino We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Smaran Thoomu 23,095 Reputation points Microsoft External Staff Moderator

2025-05-06T12:43:52.3233333+00:00

@Brian Daddino We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Venkat Reddy Navari 1,590 Microsoft External Staff Moderator

Hi @Brian Daddino The issue you're facing is due to a known limitation. Self-hosted Integration Runtime (SHIR) is not supported in Mapping Data Flows, which is why your linked service works in pipelines but fails in Data Flows.

Recommended Approaches:

Staging Layer Approach: Use a Copy Activity to move data from your on-prem SQL Server to Azure Blob Storage, then use that as the source in your Data Flow.
For more detailed information, you can refer to https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview
Managed VNET IR: Set up a Managed Virtual Network Integration Runtime with a Private Endpoint to access your on-prem SQL directly from Data Flows.
For more detailed information, you can refer to https://learn.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Brian Daddino 0 Reputation points

2025-05-01T15:58:59.1133333+00:00

thanks for this but I am trying to pull data via Stored Procedure. For copy activity I seem to need a dataset which I cannot create from a stored procedure from an on prem SQL database
AnnuKumari-MSFT 34,541 Reputation points Microsoft Employee Moderator

2025-05-02T04:16:09.1333333+00:00

Hi Brian Daddino ,

I understand you are trying to pull data from on prem SQL server via ADF. However, Really sorry but quite frankly, you ask is not clear here.

What is the need to use dataflow here? As mentioned by Venkat, For connecting to on-prem datastores, you would need Self hosted IR but SHIR is not supported in Mapping dataflow.

In case you are thinking to use stored procedure activity or copy activity, you could do that . As rightly mentioned by you , for copy activity, you would need dataset which can be created on top of the linked service for the onprem sql.

Could you please share the screenshot of where you are stuck? Thankyou
Smaran Thoomu 23,095 Reputation points Microsoft External Staff Moderator

2025-05-05T08:41:56.4733333+00:00

@Brian Daddino We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Linked Service to on prem SQL server works everywhere except in data flow

1 answer

Your answer