Scenario
Want to write data into a SQL dedicated pool using a pipeline in Synapse Workspace.
In a copy activity I read data from ADLS V2 and write into the sql database.
If I use the “copy command” option I got the error :
Error Details
ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Not able to validate external location because The remote server returned an error: (403) Forbidden.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105215,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105215,State=1,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,},],'Solution
Modify the pipelines to use bulk insert or PolyBase instead of the ‘copy command’ option.
Reason
If you’re using Synapse workspace, note using Managed Identity for authentication is not yet supported for PolyBase/COPY Command.
Azure Storage has implemented the same feature that allows you to limit connectivity to your Azure Storage account. If you choose to use this feature with an Azure Storage account that SQL Database is using, you can run into issues.
PolyBase and the COPY statement are commonly used to load data into Azure Synapse Analytics from Azure Storage accounts for high throughput data ingestion. If the Azure Storage account that you’re loading data from limits accesses only to a set of virtual network subnets, connectivity when you use PolyBase and the COPY statement to the storage account will break. For enabling import and export scenarios by using COPY and PolyBase with Azure Synapse Analytics connecting to Azure Storage that’s secured to a virtual network, follow the steps in this section: Azure Synapse Analytics PolyBase and COPY statement.
