How to use Azure Key Vault in SSIS to read secret key

[hardik]

Introduction

If you’re developing secure ETL workflows in SSIS and need to retrieve secret values such as passwords, API keys, or connection strings securely, this guide will assist you. In this guide, we’ll walk through how to use the Azure Key Vault REST API with the ZappySys SSIS PowerPack to fetch secret values dynamically.

If you’re developing secure ETL workflows in SSIS and need to retrieve secret values such as passwords, API keys, or connection strings securely, this guide will assist you.

Use Case

Many organizations store sensitive information in Azure Key Vault to avoid hardcoding secrets in applications or ETL code. This guide helps you read those secrets at runtime using:

• SSIS + ZappySys REST API Task
• Azure App Registration
• OAuth2 Client Credentials Flow

Prerequisites

Before we start, ensure you have:

• Azure Key Vault (A vault with at least one stored secret)
• Azure App Registration with access to the Key Vault (via RBAC or Access Policies)
• ZappySys SSIS PowerPack (Installed & licensed or in trial)
• SSIS via SQL Server Data Tools (Visual Studio 2015 or later)

Steps to Read Azure Key Vault Secret in SSIS

1. Create Azure App Registration (Service Principal)

1. Go to the Azure Portal → Azure Active Directory → App registrations → New registration.
2. Enter a name (e.g., SSIS_KeyVaultApp) and select the appropriate account type (e.g., Single Tenant).
3. After the app is registered:

• Copy the Application (Client) ID
• Go to Certificates & secrets, and create a new Client Secret. Copy the generated value.
• Note down your Tenant ID from the overview page.

2. Assign Key Vault Access to the App

You can grant access using either RBAC or Access Policies, depending on how your Key Vault is configured.

Option 1: Role-Based Access Control (RBAC)

• Go to your Key Vault → Access control (IAM).
• Click Add role assignment.
• Assign the Key Vault Secrets User role to your registered app.

OR

Option 2: Access Policies

• Go to your Key Vault → Access Policies.
• Click Add Access Policy.
• Grant the GET permission for Secrets.
• Select your registered app and save the policy.

3. Get Token via OAuth2 (Client Credentials Flow)

To access Azure Key Vault, you need to obtain an OAuth2 token using the Client Credentials Flow.

Token Endpoint:

https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token

Request Body (x-www-form-urlencoded):

client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
&client_secret=xxxxx
&scope=https://vault.azure.net/.default
&grant_type=client_credentials

You can test this using Postman before configuring it in SSIS.

4. Configure SSIS OAuth Connection Manager with OAuth2

1. Open your SSIS package and drag REST API Task.
2. Click New on OAuth Connection.
3. Choose OAuth2 as Authentication Type.
4. Fill in the required fields:

OAuth2 Settings:

• Token URL:

https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token

• Client ID: From your app registration

• Client Secret: From your app registration

• Scope:

https://vault.azure.net/.default

• Grant Type: Client Credentials

• Retain Token: (Checked)

You should see a configuration screen like this:

5. Call the Azure Key Vault REST API in the REST API Task

Use the following URL:

GET https://<your-keyvault-name>.vault.azure.net/secrets/<your-secret-name>?api-version=7.3

Set Accept = application/json in headers.

6. Parse the Secret Value using JSON Parser

1. Drag a Data Flow Task and add JSON Parser Transform.
2. Load the response from the API and parse the value property.
3. Pass the extracted secret to downstream tasks (e.g., SQL Connection Manager, Script Task, etc.).

Sample Output

Example response from Azure Key Vault:

{
  "value": "SuperSecret123!",
  "id": "https://<your-vault>.vault.azure.net/secrets/api-key/abcd1234",
  "attributes": { ... }
}

Extract and use the value field as needed.

Tips & Troubleshooting

• If you encounter a 401 Unauthorized error, check the following:
  1. The app registration has correct permissions.
  2. The app is properly granted access via Access Policies or RBAC on the Key Vault.
• Use the ZappySys Debug Log Viewer for troubleshooting.
• Test the Token URL and Key Vault URL in Postman first to verify external dependencies.

Conclusion

You’ve now learned how to securely integrate Azure Key Vault with SSIS using the ZappySys PowerPack. This approach helps eliminate hardcoded secrets and improves overall security in your ETL workflows.

Let us know in the comments if you’d like a follow-up article on:

• Writing secrets to Key Vault from SSIS, or
• Using Key Vault with dynamic connection managers.

For more API integrations, explore the full capabilities of the ZappySys SSIS Powerpack.

References

OAuth Connection Manager documentation
REST API Authentication with OAuth
REST API Web Service Task
SSIS REST API Web Service Task – Blog Articles
SSIS Powerpack

Contact us

If you need further assistance or have specific use-cases, contact our support team via chat or by submitting a ticket.