In my last post, I talked about the process of rotating your encryption keys. It’s just one of those routine maintenance tasks that need to be done from time to time in order to keep your encryption strong for the long haul. One type of rotation I didn’t address in that post was rotation for Always Encrypted, SQL Server’s newest form of encryption.
If you recall, Always Encrypted has two associated keys: a Column Master Key and a Column Encryption Key. The Column Encryption Key (CEK) is a symmetric key, stored in SQL Server. Like other symmetric keys, the CEK is not changed during a rotation. The Column Master Key (CMK), on the other hand, is a certificate, similar to the certificates we’ve used for transparent data encryption and for in-column encryption, and it therefore needs to be rotated regularly. The biggest difference is that the CMK is stored outside of SQL Server, in the Windows certificate store by default, so DBAs may need assistance from their Windows administrators or security administrators.
The first step of the rotation is to generate a new key. SQL Server Management Studio provides a convenient user interface for this under [your database name] > Security > Always Encrypted Keys > Column Master Keys in the Object Explorer. Right-click that folder and select New Column Master Key….
Give the new key a name, then select the location for the key. By default, you’ll see two options – Windows Certificate Store – Current User and Windows Certificate Store – Local Machine. For a shared environment (like production), you’ll want to put the new key into the Local Machine store so all users have access to it; for a lab environment, you can use either. Once you’ve selected the location, click Generate Certificate, located below the list of keys at the bottom right. When the new key appears in the list, click OK to save your changes.
Now, to rotate the key, browse the Object Explorer to [your database name] > Security > Always Encrypted Keys > Column Master Keys, then right-click the key you want to rotate.
Select Rotate, then select the new key from the wizard that follows. After a second or two, the window should close, indicating that the rotation is complete.
Finally, don’t forget to duplicate your new key to all client machines that need to be able to decrypt the data protected by Always Encrypted.
Note that all of this can also be automated with PowerShell. Microsoft has a great article on the subject in Microsoft Docs, containing instructions and examples for environments both with and without separation of duties.
The post Rotating encryption keys for Always Encrypted appeared first on The Data Files.
