2

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?

4

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
  • Thanks @Nikhil Vithlani. I Know it doesn't validate the certificate but wanted to see how to rotate the key in PROD. Actually, I need to know how to rotate the CMK without using powershell.. – Sri Oct 11 '17 at 20:58
  • Saran, Please see my edited answer – Nikhil Vithlani - Microsoft Oct 13 '17 at 1:24
  • Thanks @Nikhil for your valuable answers. I'm concerned about the steps 2 and 3 where you have mentioned to create an instance of SqlColumnEncryptionKeyStoreProvider class which requires me to write some .NET code. Is there any equivalent steps that can be executed in SSMS? My requirement is to rotate the keys using the Management Studio(SSMS) only. Is that technically possible? – Sri Oct 16 '17 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.