Azure Synapse Pipline to Azure SQL server failure

Question

Hi Team,

I've got an Azure synapse pipeline for copying data from Azure Data Lake using azure synapse server less SQL pool to SQL server (using the Self-Hosted Integration Runtime). The SQL server is in Azure VM but on a different subscription.

I can preview the source data, sink data and mapping by importing schemas however when I run the pipeline it fails with the following error:

{
"errorCode": "2200",
"message": "ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'Mysynapse Workspace-ondemand.sql.azuresynapse.net', Database: 'DatabaseName', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10060,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10060,State=0,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.),},],''Type=System.ComponentModel.Win32Exception,Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond,Source=,'",
"failureType": "UserError",
"target": "copy stg mas reports from synapse stg to ycr",
"details": []
}

I have 'Allowed Azure services and resources to access this workspace' in Synapse Workspace Networking settings from Azure portal and have added IP address as well.

Can you please advise how to fix this?

Answer 1

Hi @Awais Iqbal ,
Welcome to Microsoft Q&A platform and thanks for posting your query here.
As I understand your issue, you are trying to copy data from ADLS to serverless SQL pool but are facing connection errors. Please let me know if my understanding is incorrect.

Kindly try creating Managed private end point within your synapse workspace to mitigate the issue.

Please refer to the following document for more details on how to do the same : Synapse Managed private endpoints

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators