Sql-server – How to renew or extend the CMK Certificate in Always Encrypted feature

always-encryptedsql serversql-server-2016

I have implemented Always Encrypted feature in SQL Server 2016. The CMK is valid for one year. How do I renew it, or extend the validity?

Best Answer

Always encrypted does not check for validity of the CMK certificate explicitly. Always Encrypted treats the CMK certificate purely as (PK, SK) pair. so you can use the expired cert without running into any issues. However, the best practice is to rotate the CMK at regular intervals. Always Encrypted provides a mechanism to rotate CMK without incurring any application downtime. Please read the following official articles for details.
Article 1
Article 2

How to do CMK rotation without powershell:

  1. Provision a new CMK (CMK2) (Create a new Certificate or create a new AKV key)
  2. Use an appropriate instance of SqlColumnEncryptionStoreProvider.DecryptColumnEncryptionKey(String, String, Byte[]) method to decrypt your current encrypted CEK (CEK1) value (CekVal1)
  3. Use an appropriate instance of SqlColumnEncryptionStoreProvider.EncryptColumnEncryptionKey(String, String, Byte[]) method to encrypt the cek with the new CMK (CMK2) to get new encrypted CEK value (CekVal2).
  4. Use Create Column Master Key T-SQL to specify the new CMK (CMK2)
  5. Use Alter Column encryption key T-SQL to ADD CekVal2 to your current CEK (CEK1)
  6. At this point, your clients will be able to decrypt CEK1 with both CMK1 and CMK2.
  7. After you have successfully distributed CMK2 to all clients that need it, Use Alter Column encryption key T-SQL to DROP CekVal1 from your current CEK (CEK1)
  8. After this point, your clients will no longer be able to decrypt CEK1 using CMK1
Related Question