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.
I disagree with your statement to put the cert into the machine store so that all users have access.
That’s just it, all users shouldn’t and probably wouldn’t need or be granted access to the key.
In shared environments, user a logs onto the dispatch and invoice pc and is not granted access to encrypted data.
Later in the day user b logs onto the dispatch and logs onto the dispatch and invoice pc and is granted access to encrypted data.
This doesn’t work when the cert is in the machine store
Thanks for catching that! When I wrote that statement, I was thinking about a shared application or web server that users never touch. For a situation like you describe where a single machine is shared by multiple interactive users, you would want to control each user’s access. You describe one way to do that – by pushing the certificate into the Current User certificate store for each valid user. Alternatively, the certificate store has permissions that can be set to allow or disallow access to the certificate for each user, which would then allow the certificate to be centrally located in the Local Machine certificate store.
It’s not about single server environments as they typically never are. It’s about whether you store the cert in the machine store on any server\pc that’s used to connect, or whether you lock things down.
Remember even a pc that is “single use” may one day be logged onto by joe user and there’s more than enough info on the web to instruct non Dbas to connect via ssms and view encrypted data if the cert is in place.