Introduction SQL Server Always Encrypted uses column master keys (CMK) stored as certificates to encrypt and decrypt sensitive data. When the CMK certificate expires, applications can no longer decrypt encrypted columns, causing query failures and data access outages.

Symptoms - `CryptographicException: The certificate has expired` in application logs - Queries against encrypted columns return garbled binary data or errors - `Cannot decrypt column 'ssn' using column encryption key` errors - SSMS shows certificate with expired date in Always Encrypted key management - Application starts fail due to inability to initialize the encryption provider

Common Causes - Certificate created with a short validity period (e.g., 1 year) - No certificate renewal process in place - Certificate stored in Windows Certificate Store without auto-renewal - Multiple applications sharing the same CMK with no rotation plan - Azure Key Vault CMK with expiration policy not monitored

Step-by-Step Fix 1. **Check the current CMK certificate status": ```sql -- List column master keys SELECT name, key_store_provider_name, key_path FROM sys.column_master_keys;

-- Check certificate expiration in Windows Certificate Store -- PowerShell Get-ChildItem Cert:\CurrentUser\My | Where-Object { $_.Subject -like "*Always Encrypted*" } | Select-Object Subject, NotBefore, NotAfter, Thumbprint ```

  1. 1.**Generate a new certificate":
  2. 2.```powershell
  3. 3.# Create a new self-signed certificate
  4. 4.$cert = New-SelfSignedCertificate `
  5. 5.-Subject "CN=Always Encrypted CMK" `
  6. 6.-CertStoreLocation "Cert:\CurrentUser\My" `
  7. 7.-KeyExportPolicy Exportable `
  8. 8.-Type DocumentEncryptionCert `
  9. 9.-KeyUsage DataEncipherment `
  10. 10.-NotAfter (Get-Date).AddYears(5)

# Export the certificate Export-Certificate -Cert $cert -FilePath "C:\certs\new_cmk_cert.cer" ```

  1. 1.**Register the new CMK in SQL Server":
  2. 2.```sql
  3. 3.-- Add the new CMK
  4. 4.ALTER COLUMN MASTER KEY [CMK_Auto1]
  5. 5.WITH (
  6. 6.KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
  7. 7.KEY_PATH = N'CurrentUser/My/<new_thumbprint>'
  8. 8.);
  9. 9.`
  10. 10.**Rotate the column encryption key":
  11. 11.```powershell
  12. 12.# Use SqlServer module to rotate keys
  13. 13.Import-Module SqlServer

$database = "MyDatabase" $serverInstance = "localhost"

# Rotate CEK to use the new CMK $cmk = Get-SqlColumnMasterKey -Name "CMK_Auto1" ` -Database $database -ServerInstance $serverInstance

$cek = Get-SqlColumnEncryptionKey -Name "CEK_Auto1" ` -Database $database -ServerInstance $serverInstance

# Complete the rotation # This re-encrypts the CEK with the new CMK ```

  1. 1.**Update application connection strings":
  2. 2.```csharp
  3. 3.// Ensure the application can access the new certificate
  4. 4.// The certificate must be in the same store on all application servers
  5. 5.var connString = "Data Source=server;Initial Catalog=db;" +
  6. 6."Column Encryption Setting=enabled;";
  7. 7.`

Prevention - Set CMK certificate validity to at least 5 years - Set up monitoring for certificate expiration with 90-day advance alerts - Document the certificate rotation process and include it in runbooks - Use Azure Key Vault or HSM for CMK storage with automated rotation - Test certificate rotation in staging before applying to production - Keep a backup of all CMK certificates in a secure location - Implement a key management policy with defined rotation schedules