SecretRotator.cs

using Azure.Security.KeyVault.Secrets;
using Microsoft.Data.SqlClient;
using System;
using Microsoft.Extensions.Logging;
using Azure.Identity;
using System.Security.Cryptography;


namespace Microsoft.KeyVault
{
   
    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.net";
            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");
        }

        private static void CreateNewSecretVersion(SecretClient client, KeyVaultSecret secret, string newSecretValue)
        {
            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] : "60";

            //add new secret version to key vault
            var newSecret = new KeyVaultSecret(secret.Name, newSecretValue);
            newSecret.Properties.Tags.Add(CredentialIdTag, credentialId);
            newSecret.Properties.Tags.Add(ProviderAddressTag, providerAddress);
            newSecret.Properties.Tags.Add(ValidityPeriodDaysTag, validityPeriodDays);
            newSecret.Properties.ExpiresOn = DateTime.UtcNow.AddDays(Int32.Parse(validityPeriodDays));
            client.SetSecret(newSecret);
        }

        private static void UpdateServicePassword(KeyVaultSecret secret, string newpassword)
        {
            var userId = secret.Properties.Tags.ContainsKey(CredentialIdTag) ? secret.Properties.Tags[CredentialIdTag] : "";
            var datasource = secret.Properties.Tags.ContainsKey(ProviderAddressTag) ? secret.Properties.Tags[ProviderAddressTag] : "";
            var dbResourceId = secret.Properties.Tags.ContainsKey(ProviderAddressTag) ? secret.Properties.Tags[ProviderAddressTag] : "";
            
            var dbName = dbResourceId.Split('/')[8];
            var password = secret.Value;
            
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = $"{dbName}.database.windows.net";
            builder.UserID = userId;
            builder.Password = password;
    
            //Update password
            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand($"ALTER LOGIN {userId} WITH Password='{newpassword}';", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        private static string CreateRandomPassword()
        {
            const int length = 60;
            
            byte[] randomBytes = new byte[length];
		    RNGCryptoServiceProvider rngCrypt = new RNGCryptoServiceProvider();
		    rngCrypt.GetBytes(randomBytes);
		    return Convert.ToBase64String(randomBytes);
        }
         
        private static void CheckServiceConnection(KeyVaultSecret secret)
        {
            var userId = secret.Properties.Tags.ContainsKey(CredentialIdTag) ? secret.Properties.Tags[CredentialIdTag] : "";
            var dbResourceId = secret.Properties.Tags.ContainsKey(ProviderAddressTag) ? secret.Properties.Tags[ProviderAddressTag] : "";
            
            var dbName = dbResourceId.Split('/')[8];
            var password = secret.Value;
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = $"{dbName}.database.windows.net";
            builder.UserID = userId;
            builder.Password = password;
            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                connection.Open();
            }
        }
    }
}