Azure Storage connection manager

APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

The Azure Storage connection manager enables a SQL Server Integration Services (SSIS) package to connect to an Azure Storage account. The connection manager is a component of the SQL Server Integration Services (SSIS) Feature Pack for Azure.

In the Add SSIS Connection Manager dialog box, select AzureStorage > Add.

The following properties are available.

  • Service: Specifies the storage service to connect to.
  • Account name: Specifies the storage account name.
  • Authentication: Specifies the authentication method to use. AccessKey and ServicePrincipal authentication are supported.
    • AccessKey: For this authentication method, specify the Account key.
    • ServicePrincipal: For this authentication method, specify the Application ID, Application key, and Tenant ID of the service principal. For Test Connection to work, the service principal should be assigned at least the Storage Blob Data Reader role to the storage account. For more information, see Grant access to Azure blob and queue data with RBAC in the Azure portal.
  • Environment: Specifies the cloud environment hosting the storage account.

Managed identities for Azure resources authentication

When running SSIS packages on Azure-SSIS integration runtime in Azure Data Factory, you can use the managed identity associated with your data factory for Azure storage authentication. The designated factory can access and copy data from or to your storage account by using this identity.

Refer to Authenticate access to Azure Storage using Azure Active Directory for Azure Storage authentication in general. To use managed identity authentication for Azure Storage:

  1. Find the data factory managed identity from the Azure portal. Go to your data factory's Properties. Copy the Managed Identity Application ID (not Managed Identity Object ID).

  2. Grant the managed identity proper permission in your storage account. For more details about roles, see Manage access rights to Azure Storage data with RBAC.

    • As source, in Access control (IAM), grant at least the Storage Blob Data Reader role.
    • As destination, in Access control (IAM), grant at least the Storage Blob Data Contributor role.

Then configure managed identity authentication for the Azure Storage connection manager. Here are the options to do this:

  • Configure at design time. In SSIS Designer, double-click the Azure Storage connection manager to open Azure Storage Connection Manager Editor. Select Use managed identity to authenticate on Azure.

    Note

    Currently, this option doesn't take effect (indicating that managed identity authentication doesn't work) when you run SSIS package in SSIS Designer or Microsoft SQL Server.

  • Configure at runtime. When you run the package via SQL Server Management Studio (SSMS) or Azure Data Factory Execute SSIS Package activity, find the Azure Storage connection manager. Update its property ConnectUsingManagedIdentity to True.

    Note

    In Azure-SSIS integration runtime, all other authentication methods (for example, access key and service principal) preconfigured on the Azure Storage connection manager are overridden when managed identity authentication is used for storage operations.

Note

To configure managed identity authentication on existing packages, the preferred way is to rebuild your SSIS project with the latest SSIS Designer at least once. Redeploy that SSIS project to your Azure-SSIS integration runtime, so that the new connection manager property ConnectUsingManagedIdentity is automatically added to all Azure Storage connection managers in your SSIS project. The alternative way is to directly use a property override with property path \Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] at runtime.

See also

Integration Services (SSIS) Connections