ADF PIPELINE Data copy from Azure SQL server database to onpremises SQL Server failing

Question

I have a copy data pipeline in ADF copying data from Azure SQL database to On-prem SQL database.

• Self-hosted integration is configured successfully connected to ADF
• Connection to Azure SQL database via managed private endpoint configured with System managed identity and user has been granted access to Azure SQL database.
• Copy runtime validation returns no errors
• Pipeline validation returns no errors

This is the error below; this is definitely no network related as both my SHIR and Linked service connectivity tests are successful.

Any ideas?

Answer 1

Hello Gomolemo Matsunyane,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having ADF PIPELINE Data copy from Azure SQL server database to onpremises SQL Server failing issue.

This requires a careful troubleshooting, not for generic. Check the followings before getting back:

1. Your “Test connection” and “Validate” do not execute your full path or SQL; they often pass while the runtime fails due to permissions or schema constraints. - https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities
2. If you enable a Private Endpoint and disable public access, make sure all clients (including Azure IR / ADF Managed VNet) resolve to the private FQDN; mixed setups can black‑hole traffic.- https://learn.microsoft.com/en-us/azure/azure-sql/database/network-access-controls-overview?view=azuresql and https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal-private
3. If you don’t open UDP 1434 or specify a fixed TCP port, SHIR can’t resolve the instance, use a fixed port and connect as server,port. - https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime
4. Creating the ADF MI user at the server blade (IAM) is good but not enough; you must still CREATE USER FROM EXTERNAL PROVIDER inside each database you will read.

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

Hi Gomolemo Matsunyane ,

Thank you for reaching out to Microsoft QA!
It looks like your ADF copy pipeline from Azure SQL Database to on-prem SQL Server is failing even though SHIR and connectivity tests are fine. This usually points to data or configuration issues rather than networking. Here are a few things to check:

• Data constraints: Make sure the data doesn’t violate destination constraints like non-nullable columns or unique keys. Also confirm source and target data types match.
• Mapping and settings: Verify column mappings and consider adjusting batch size to avoid memory issues.
• Transient errors: Sometimes failures are temporary, try rerunning the pipeline after a short wait.
• Logging: Enable detailed logging and review the activity run output for specific error details.
• Managed Identity permissions: Double-check that the system-managed identity has the right permissions on Azure SQL Database.
• Connector issues: Review ADF troubleshooting docs for Azure SQL connectors to rule out known issues.
• Pipeline parameters: Ensure all parameters passed are valid and in the correct format.

If the above steps don’t resolve the issue, please provide additional information such as:

1. What error message are you seeing in the pipeline execution logs?
2. Are you using any transformations in your copy activity?
3. How large is the dataset you are trying to copy?
4. Have there been any recent changes made to the SQL Server schema?

Hope this helps! Let me know if you need more guidance or clarification on any of the steps!

Answer 3

Firewall rule was created to allow SHIR to communicate with AZURE SQL server on port 1433 and the pipeline was successful.