Automate the rotation of a secret for resources that use one set of authentication credentials
The best way to authenticate to Azure services is by using a managed identity, but there are some scenarios where that isn't an option. In those cases, access keys or secrets are used. You should periodically rotate access keys or secrets.
This tutorial shows how to automate the periodic rotation of secrets for databases and services that use one set of authentication credentials. Specifically, this tutorial rotates SQL Server passwords stored in Azure Key Vault by using a function triggered by Azure Event Grid notification:
- Thirty days before the expiration date of a secret, Key Vault publishes the "near expiry" event to Event Grid.
- Event Grid checks the event subscriptions and uses HTTP POST to call the function app endpoint subscribed to the event.
- The function app receives the secret information, generates a new random password, and creates a new version for the secret with the new password in Key Vault.
- The function app updates SQL Server with the new password.
Note
There could be a lag between steps 3 and 4. During that time, the secret in Key Vault won't be able to authenticate to SQL Server. In case of a failure of any of the steps, Event Grid retries for two hours.
Prerequisites
- An Azure subscription - Create a trial subscription.
- Azure Key Vault
- SQL Server
If you don't have existing Key Vault and SQL Server, you can use this deployment link:
- Under Resource group, select Create new. Give group a name, we use akvrotation in this tutorial.
- Under SQL Admin Login, type SQL administrator login name.
- Select Review + create.
- Select Create
You'll now have a Key Vault, and a SQL Server instance. You can verify this setup in the Azure CLI by running the following command:
az resource list -o table -g akvrotation
The result will look something the following output:
Name ResourceGroup Location Type Status
----------------------- -------------------- ---------- --------------------------------- --------
akvrotation-kv akvrotation chinaeast Microsoft.KeyVault/vaults
akvrotation-sql akvrotation chinaeast Microsoft.Sql/servers
akvrotation-sql/master akvrotation chinaeast Microsoft.Sql/servers/databases
akvrotation-sql2 akvrotation chinaeast Microsoft.Sql/servers
akvrotation-sql2/master akvrotation chinaeast Microsoft.Sql/servers/databases
Create and deploy SQL server password rotation function
Important
This template requires the key vault, SQL server and Azure Function to be in the same resource group.
Next, create a function app with a system-managed identity, in addition to the other required components, and deploy SQL server password rotation functions
The function app requires these components:
- An Azure App Service plan
- A Function App with SQL password rotation functions with event trigger and http trigger
- A storage account required for function app trigger management
- An access policy for Function App identity to access secrets in Key Vault
- An Event Grid event subscription for SecretNearExpiry event
Select the Azure template deployment link:
In the Resource group list, select akvrotation.
In the SQL Server Name, type the SQL Server name with password to rotate
In the Key Vault Name, type the key vault name
In the Function App Name, type the function app name
In the Secret Name, type secret name where the password will be stored
In the Repo Url, type function code GitHub location (https://github.com/Azure-Samples/KeyVault-Rotation-SQLPassword-Csharp.git)
Select Review + create.
Select Create.
After you complete the preceding steps, you'll have a storage account, a server farm, and a function app. You can verify this setup in the Azure CLI by running the following command:
az resource list -o table -g akvrotation
The result will look something like the following output:
Name ResourceGroup Location Type Status
----------------------- -------------------- ---------- --------------------------------- --------
akvrotation-kv akvrotation chinaeast Microsoft.KeyVault/vaults
akvrotation-sql akvrotation chinaeast Microsoft.Sql/servers
akvrotation-sql/master akvrotation chinaeast Microsoft.Sql/servers/databases
cfogyydrufs5wazfunctions akvrotation chinaeast Microsoft.Storage/storageAccounts
akvrotation-fnapp akvrotation chinaeast Microsoft.Web/serverFarms
akvrotation-fnapp akvrotation chinaeast Microsoft.Web/sites
akvrotation-fnapp akvrotation chinaeast Microsoft.insights/components
For information on how to create a function app and use managed identity to access Key Vault, see Create a function app from the Azure portal, How to use managed identity for App Service and Azure Functions, and Assign a Key Vault access policy using the Azure portal.
Rotation function
Deployed in previous step function uses an event to trigger the rotation of a secret by updating Key Vault and the SQL database.
Function trigger event
This function reads event data and runs the rotation logic:
public static class SimpleRotationEventHandler
{
[FunctionName("AKVSQLRotation")]
public static void Run([EventGridTrigger]EventGridEvent eventGridEvent, ILogger log)
{
log.LogInformation("C# Event trigger function processed a request.");
var secretName = eventGridEvent.Subject;
var secretVersion = Regex.Match(eventGridEvent.Data.ToString(), "Version\":\"([a-z0-9]*)").Groups[1].ToString();
var keyVaultName = Regex.Match(eventGridEvent.Topic, ".vaults.(.*)").Groups[1].ToString();
log.LogInformation($"Key Vault Name: {keyVaultName}");
log.LogInformation($"Secret Name: {secretName}");
log.LogInformation($"Secret Version: {secretVersion}");
SecretRotator.RotateSecret(log, secretName, keyVaultName);
}
}
Secret rotation logic
This rotation method reads database information from the secret, creates a new version of the secret, and updates the database with the new secret:
public class SecretRotator
{
private const string CredentialIdTag = "CredentialId";
private const string ProviderAddressTag = "ProviderAddress";
private const string ValidityPeriodDaysTag = "ValidityPeriodDays";
public static void RotateSecret(ILogger log, string secretName, string keyVaultName)
{
//Retrieve Current Secret
var kvUri = "https://" + keyVaultName + ".vault.azure.cn";
var client = new SecretClient(new Uri(kvUri), new DefaultAzureCredential());
KeyVaultSecret secret = client.GetSecret(secretName);
log.LogInformation("Secret Info Retrieved");
//Retrieve Secret Info
var credentialId = secret.Properties.Tags.ContainsKey(CredentialIdTag) ? secret.Properties.Tags[CredentialIdTag] : "";
var providerAddress = secret.Properties.Tags.ContainsKey(ProviderAddressTag) ? secret.Properties.Tags[ProviderAddressTag] : "";
var validityPeriodDays = secret.Properties.Tags.ContainsKey(ValidityPeriodDaysTag) ? secret.Properties.Tags[ValidityPeriodDaysTag] : "";
log.LogInformation($"Provider Address: {providerAddress}");
log.LogInformation($"Credential Id: {credentialId}");
//Check Service Provider connection
CheckServiceConnection(secret);
log.LogInformation("Service Connection Validated");
//Create new password
var randomPassword = CreateRandomPassword();
log.LogInformation("New Password Generated");
//Add secret version with new password to Key Vault
CreateNewSecretVersion(client, secret, randomPassword);
log.LogInformation("New Secret Version Generated");
//Update Service Provider with new password
UpdateServicePassword(secret, randomPassword);
log.LogInformation("Password Changed");
log.LogInformation($"Secret Rotated Successfully");
}
}
You can find the complete code on GitHub.
Add the secret to Key Vault
Set your access policy to grant manage secrets permissions to users:
az keyvault set-policy --upn <email-address-of-user> --name akvrotation-kv --secret-permissions set delete get list
Create a new secret with tags that contain the SQL Server resource ID, the SQL Server login name, and validity period for the secret in days. Provide name of the secret, initial password from SQL database (in our example "Simple123") and include an expiration date that's set for tomorrow.
$tomorrowDate = (get-date).AddDays(+1).ToString("yyy-MM-ddThh:mm:ssZ")
az keyvault secret set --name sqlPassword --vault-name akvrotation-kv --value "Simple123" --tags "CredentialId=sqlAdmin" "ProviderAddress=<sql-database-resource-id>" "ValidityPeriodDays=90" --expires $tomorrowDate
Creating a secret with a short expiration date will publish a SecretNearExpiry event within 15 minutes, which will in turn trigger the function to rotate the secret.
Test and verify
To verify that the secret has rotated, go to Key Vault > Secrets:
Open the sqlPassword secret and view the original and rotated versions:
Create a web app
To verify the SQL credentials, create a web app. This web app will get the secret from Key Vault, extract SQL database information and credentials from the secret, and test the connection to SQL Server.
The web app requires these components:
- A web app with system-managed identity
- An access policy to access secrets in Key Vault via web app managed identity
Select the Azure template deployment link:
Select the akvrotation resource group.
In the SQL Server Name, type the SQL Server name with password to rotate
In the Key Vault Name, type the key vault name
In the Secret Name, type secret name where the password is stored
In the Repo Url, type web app code GitHub location (https://github.com/Azure-Samples/KeyVault-Rotation-SQLPassword-Csharp-WebApp.git)
Select Review + create.
Select Create.
Open the web app
Go to the deployed application URL:
https://akvrotation-app.azurewebsites.net/
When the application opens in the browser, you will see the Generated Secret Value and a Database Connected value of true.