Synapse Analytics Copy Activity Sink dedicated SQL Pool - Bulk Insert is Working but Copy command doesn't

Question

Hi everyone,

I want to write data into a SQL dedicated pool using a pipeline in Synapse Workspace.

I created a dataset and successfully tested the connection.

I'm using a linked service together with azure key vault.

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 :

{
    "errorCode": "2200",
    "message": "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.,},],'",
    "failureType": "UserError",
    "target": "CD_EDL_STG_DWH",
    "details": []
}

Whereas with bulk insert I have no problems. I still need to test it with Polybase.

Any ideas?

I found some article for azure data factory saying the following:

using Azure Key Vault to store authentication credentials, which is an un-supported managed identity authentication method at this time for using PolyBase and Copy command.

Is that also true and valid for Synapse Workspaces?

Here the source: https://www.mssqltips.com/sqlservertip/6350/load-data-lake-files-into-azure-synapse-analytics-using-azure-data-factory/

BR.
Paul

Answer 1

Hello @Paul Hernandez ,

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.

Hope this helps. Do let us know if you any further queries.

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

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.