Copy Statement Connection

The Azure Synapse SQL destination creates a connection to issue a COPY command that reads from the staging area and bulk loads the data into Azure Synapse.

The destination uses the connection information specified for the Azure Synapse and staging connections for the copy statement connection. That is, the copy statement connection uses configuration details for the Azure Synapse server and database that you define for the Azure Synapse connection. The copy statement connection also uses configuration details for the staging storage account and container that you define for the staging connection.

By default, the copy statement connection also uses the staging authentication method and configuration details that you define for the staging connection. When needed, you can specify authentication details specifically for the copy statement connection, instead of reusing the staging authentication details.

On the Azure Staging tab, you can enable the use of copy statement authentication and specify the authentication method and details to use.

You can use the following authentication methods for the copy statement connection:
Azure Active Directory User
Can be used only when the Azure Synapse connection uses Azure Active Directory authentication.
The destination connects from Azure Synapse to the staging area using the Azure Active Directory authentication configured for the storage account.
For information about Azure Active Directory, see the Azure documentation.
The minimum required RBAC roles are Storage Blob Data Contributor or Storage Blob Data Owner for the storage account.
Azure Active Directory with Service Principal
The destination connects from Azure Synapse to the staging area using the following information:
  • Application ID - Application ID for the Azure Active Directory Data Collector application. Also known as the client ID.

    For information on accessing the application ID from the Azure portal, see the Azure documentation.

  • Tenant ID - Tenant ID for the Azure Active Directory Data Collector application.
  • Application Key - Authentication key or client secret for the Azure Active Directory application. Also known as the client secret.

    For information on accessing the application key from the Azure portal, see the Azure documentation.

The minimum required RBAC roles are Storage Blob Data Contributor, Storage Blob Data Owner, or Storage Blob Data Reader.
Managed Identity
The destination connects from Azure Synapse to the staging area using a managed identity. You can use this authentication method when your storage account is attached to a VNet.
For information about setting up managed identities, see the Azure documentation.
The minimum required RBAC roles are Storage Blob Data Contributor or Storage Blob Data Owner for the AAD-registered SQL database server.
Shared Access Signature (SAS)
The destination connects from Azure Synapse to the staging area using an SAS token. The SAS token must be configured to allow all permissions and the HTTPS protocol.

You can create the SAS token using the Azure portal by selecting Shared Access Signature from Settings in the storage account menu. Or you can create the SAS token using the Azure CLI as described in the Azure documentation.

Copy and save the generated token so that you can use it to configure the destination.

The minimum required permissions are Read and List.

Storage Account Key
The destination connects from Azure Synapse to the staging area using the following information:
  • Account Shared Key - Shared access key that Azure generated for the storage account.

    For more information on accessing the shared access key from the Azure portal, see the Azure documentation.

The following image displays a destination configured to use the Managed Identity authentication method for copy statement authentication:

The destination also uses a staging connection that connects to the my_files file system in the my_storage Azure Data Lake Storage Gen2 storage account using Storage Account Key authentication. If you did not configure copy statement authentication, the destination would use the specified storage account key for copy statement authentication.