Azure Storage connection manager

Applies to: yesSQL Server (all supported versions) yes SSIS Integration Runtime in Azure Data Factory

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, ServicePrincipal, and SharedAccessSignature 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.
    • SharedAccessSignature: For this authentication method, specify at least the Token of the shared access signature. To test connection, specify additionally the resource scope to test against. It may be Service, Container, or Blob. For Container and Blob, specify container name and blob path, respectively. For more information, see Azure Storage shared access signature overview.
  • Environment: Specifies the cloud environment hosting the storage account.

Managed identities for Azure resources authentication

When running SSIS packages on Azure-SSIS integration runtime (IR) in Azure Data Factory (ADF), you can use Azure Active Directory (AAD) authentication with the specified system/user-assigned managed identity for your ADF to access Azure Storage. Your Azure-SSIS IR can access and copy data from or to your storage account by using this managed identity.

Refer to the Authenticate access to Azure Storage using AAD article for Azure Storage authentication in general. To use AAD authentication with the specified system/user-assigned managed identity for your ADF to access Azure Storage, follow these steps:

  1. Find the specified system/user-assigned managed identity for your ADF from Azure portal. Go to your data factory's Properties. Copy the Managed Identity Application ID (not the Managed Identity Object ID).

  2. Grant the specified system/user-assigned managed identity for your ADF the required permissions to access Azure Storage. For more details about roles, see the Manage access rights to Azure Storage data with RBAC article.

    • 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.

Finally, you can configure AAD authentication with the specified system/user-assigned managed identity for your ADF on the Azure Storage connection manager. Here are the options to do this:

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

    Note

    Currently, this option doesn't take effect (indicating that AAD authentication with the specified system/user-assigned managed identity for your ADF doesn't work) when you run your package in SSIS Designer or on SQL Server.

  • Configure at run time. When you run your package via SQL Server Management Studio (SSMS) or Execute SSIS Package activity in ADF pipeline, find the Azure Storage connection manager and update its property ConnectUsingManagedIdentity to True.

    Note

    On Azure-SSIS IR, all other authentication methods (for example, integrated security and password) preconfigured on your Azure Storage connection manager are overridden when using AAD authentication with the specified system/user-assigned managed identity for your ADF.

To configure AAD authentication with the specified system/user-assigned managed identity for your ADF on your existing packages, the preferred way is to rebuild your SSIS project with the latest SSIS Designer at least once. Redeploy your SSIS project to run on Azure-SSIS IR, so that the new connection manager property ConnectUsingManagedIdentity is automatically added to all Azure Storage connection managers in your project. The alternative way is to directly use property overrides with the property path \Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] assigned to True at run time.

Secure network traffic to your storage account

ADF is now a trusted Microsoft service to Azure Storage. When you use AAD authentication with the specified system/user-assigned managed identity for your ADF, it's possible to secure your storage account by limiting access to selected networks while still allowing your ADF to access it. Please refer to the Managing exceptions article for instructions.

See also