Copy Activity Failure - Random

Question

I have several pipelines that do parallel copy activities to an Azure SQL Sink. I get failures on some copy activities a couple of times a day. It is different copy activities from different pipelines each day. These pipelines are triggered throughout the day but running them manually is OK, so I'm not able to reproduce the error consistently.

All Copy activities are in the same tenant, the ADF Managed System Identity has db_owner rights to the SQL db. The copy activities drop and auto-create tables in the sink.

This is the error I'm getting:

Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly. External access policies needed to validate access are either expired or not found. To force a policy fetch, execute the procedure 'sys.sp_external_policy_refresh'. If the error persists, please check provided errors related to external access policy fetch. External access policies needed to validate access are either expired or not found. To force a policy fetch, execute the procedure 'sys.sp_external_policy_refresh'. If the error persists, please check provided errors related to external access policy fetch.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4002,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=4002,State=2,Message=The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly.,},{Class=0,Number=372,State=1,Message=External access policies needed to validate access are either expired or not found. To force a policy fetch, execute the procedure 'sys.sp_external_policy_refresh'. If the error persists, please check provided errors related to external access policy fetch.,},{Class=0,Number=372,State=1,Message=External access policies needed to validate access are either expired or not found. To force a policy fetch, execute the procedure 'sys.sp_external_policy_refresh'. If the error persists, please check provided errors related to external access policy fetch.,},],'

Answer 1

Hi @tienluong_pgr

Welcome to Microsoft Q&A forum and thanks for reaching out here.

From the error message my understanding is that it is coming from your Azure SQL Server. It seems to be related to Access policies. I would also recommend going through the below article and see if that helps to troubleshoot the access policies.

Troubleshooting external data and access policies in Azure SQL and SQL Server

This error can occur when there is a delay in the policy refresh process, which can cause the policies to expire before they are refreshed. This can happen randomly and may not be reproducible consistently.

As a workaround, if the user account you are using to connect to Azure SQL has permissions to execute the sys.sp_external_policy_refresh SP, then you may try executing the sys.sp_external_policy_refresh procedure (For incremental policy refresh - EXEC sp_external_policy_refresh and for Complete Policy refresh - EXEC sp_external_policy_refresh @type = 'reload' ) using the Pre-copy script in copy activity before writing data to your SQL Sink.

Hope this helps. If this doens't work and the issue still persists, I recommend filing a support ticket for deeper analysis. As the issue is on Azure SQL side, please file a support ticket for Azure SQL Server product and one of the enigneer can help troubleshoot and provide the root cause and possible solution.

Let us know how it goes.

Thank you