Once you have Always Encrypted set up you may find that you need to change (rotate) the encryption key that you used to set up the initial encryption. This could be due to an expiring certificate, security policy that states you need to rotate keys on a schedule or maybe you are moving the database to a different domain. There is a fantastic SQL Server Central blog post by Ed Leighton-Dick that goes into good detail and was where I learned how to rotate keys. This post may do a more through job or do a deeper dive into key rotation than I will in this post. This post is a relatively straight forward "down and dirty" how to guide. If you read my "Decoder Ring Not Needed" post, you may remember that I showed two ways of setting up Always Encrypted. The first method was to auto-generate the keys and certificates. I have not used that method for production however if you initially set up your environment that way and want to switch to using a different certificate for encryption this should help you. I will not be going over the certificate piece again, for that please refer to the original post.
Expanding out the sections under Always Encrypted Keys we can see the existing Column Master Key and Column Encryption Key.
Right click on the Column Master Key folder and choose New Column Master Key from the context menu.
The New Column Master Key GUI will pop up. Give it a unique name and choose the appropriate certificate from the correct Key store. Then click OK to create the new key.
You should now see both the old and new Column Master Keys.
Right click on the old Column Master Key and choose Rotate from the context menu.
Make sure the new Column Master Key is selected as the target. You will note that it shows which Column Encryption Keys will be affected by this rotation. Click OK to rotate the keys.
Right click on the Column Encryption Key and view the properties. You will see both Column Master Keys. This is fine. If you need to install the new certificate, if that is the reason for rotating keys, on additional servers make sure you get that done before proceeding to the next step which is cleanup. The key rotation process is very quick since it is only changing the encryption of the Column Encryption Key, the encrypted data is not changed or re-encrypted by this process.
Again, right click on the old Column Master Key and choose Cleanup from the context menu.
Verify that the correct key is in bold and click OK.
If you check the properties of the Column Encryption Key again you should only see the new Column Master Key.
Finally, right click on the old Column Master Key and delete it.
I hope you find this quick guide to rotating the keys useful.
