SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Rotating encryption keys for Always Encrypted

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….

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.

New Column Master Key Dialog

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.

Rotate Column Master Key

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.

Rotate Column Master Key Dialog

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.

The Data Files

Ed Leighton-Dick is a SQL Server performance and architecture expert and Founder/Principal Consultant of Kingfisher Technologies, a consultancy focused on SQL Server performance, architecture, and security. He is a frequent volunteer with PASS, including roles as regional mentor, chapter leader of I-380 PASS SQL Server User Group, and organizer of SQL Saturday Iowa City. He can often be found teaching sessions at local, regional, and national events, including user groups, SQL Saturday events, and Iowa Code Camp. Ed can be reached through his blog, edleightondick.com, and on Twitter at @eleightondick.

Comments

Leave a comment on the original post [edleightondick.com, opens in a new window]

Loading comments...