How to rotate the CMK of Always Encrypted using SSMS?

Posted on

Question :

I have implemented the encryption in SQL Server 2016 using Always Encrypted.

I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio 17.0.

I’m looking for the steps that can executed using only SSMS and not PowerShell or any other program.

Is it technically possible to rotate the key only using SSMS?

Experts advice please.

Answer :

If you look at ALTER COLUMN ENCRYPTION KEY, e.g. at MS:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-column-encryption-key-transact-sql

There are options to both ADD a Column Master Key (start rotation) and DROP (finish rotation).

First Create new Column master key using management studio then right click on old CMK and select Roate option. (using Management studio 2017.
In Target dropdown select new column master key.

Below should be the right and easy MS link for key rotation using powershell

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/rotate-always-encrypted-keys-using-powershell?view=sql-server-2017

Leave a Reply

Your email address will not be published. Required fields are marked *