Lesson Learned #422: Retrieving Database Connection Strings with Azure Key Vault

Today, we worked on a service request that our customer wants to use Azure Key Vault for saving the connectionstring for their Azure SQL Database or Managed Instance. Azure Key Vault (AKV) provides a secure and centralized way of managing sensitive data such as secrets, encryption keys, and certificates. In this article, we will focus on securely retrieving a database connection string using AKV.

I would like to share an example about it, based on this article: Quickstart - Set & retrieve a secret from Key Vault using PowerShell | Microsoft Learn

Why Azure Key Vault?

• Centralized Management: All secrets are stored in one place, making it easier to manage and rotate them as needed.

• Secure: Data is encrypted at rest and in transit.

• Logging and Monitoring: AKV integrates with Azure logging and monitoring tools to keep track of how and when secrets are accessed.

• Access Control: With Azure Active Directory integration, you can specify which users or applications can access specific secrets.

Retrieving a Connection String Using PowerShell

Below is a simplified PowerShell script that demonstrates how to:

1. Set the Azure context to a specific subscription.
2. Retrieve a connection string from an Azure Key Vault.
3. Use this connection string to establish a connection to an Azure SQL Database.

# Connect to Azure account
Connect-AzAccount

# Select the subscription
$subscriptionId = "YourSubscriptionId"
Set-AzContext -SubscriptionId $subscriptionId

$resourceGroupName = "YourResourceGroupName"
$keyVaultName = "YourKeyVaultName"
$connectionStringSecretName = "YourSecretName"

# Get KeyVault 
$keyVault = Get-AzKeyVault -ResourceGroupName $resourceGroupName -VaultName $keyVaultName -ErrorAction SilentlyContinue

# Retrieve the connection string from the AKV
$retrievedConnectionString = Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $connectionStringSecretName -AsPlainText

# Connect to Azure SQL Database using SQLClient
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $retrievedConnectionString

try {
    $connection.Open()
    Write-Output "Connection established successfully"
} catch {
    Write-Error "Error connecting: $_"
} finally {
    $connection.Close()
}

Enjoy!