synapse analytics copy data ErrorCode=SqlFailedToConnect

Question

Hello everyone,

we want to transfer data from a Synapse Analytics Workspace to a self-hosted SQL Server.

After building a test environment and successful tests, we want to build the prod environment.

While testing the pipelines in the prod environment we get the following error on the "Copy data" task:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'datalakeworkspace-ondemand.sql.azuresynapse.net', Database: 'Prod_DataLake', User: 'sqluser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access

{
    "errorCode": "2200",
    "message": "ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'datalakeworkspace-ondemand.sql.azuresynapse.net', Database: 'Prod_DataLake', User: 'sqluser'. 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server),Source=.Net SqlClient Data Provider,SqlErrorNumber=53,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=53,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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server),},],''Type=System.ComponentModel.Win32Exception,Message=The network path was not found,Source=,'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

(connection info was changed)

• We are using a linked server to the synapse azure database itself as Source The error message uses the Source connection variables.
• "test connection" function is successful (Linked service and dataset)
• In previous tasks we used the same linked service and data set and we can access the data (Get Metada; SELECT-Script)
• The synapse azure database can be accessed by SQL Management Studio from non-cloud systems
• The sql-user is the default admin user and can access the data
• Test-system does not have the problem
• We checked all settings with the test-system to be the same, except name changes
• Network settings on the snypase workspace are on default settings (same as test)

Linked Service config:

I do not understand what the "copy data" function does different to a normal select.

Any help is appreciated.