Changing Database Encryption Keys

Question

I have been asked to "rotate" the Certs and Keys for a given database and the only way I knew of to accomplish this was to unencrypt the data first, using DECRYPTION BY CERTIFICATE, create new certs and keys, and re-encrypt using the new cert and key.

I was then told that I could not have the data in an unencrypted state (readable to users) for any amount of time and the application could not be brought down to ensure security for any amount of time. Then I found ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD

My question is, will this REGENERATE accomplish the "rotation" of certs and keys (create new ones, which I think it will), and if so, will it leave the data in a "readable" state at anytime during the process?

SQL Server 2019 Standard Edition build 15.0.4083.2

Answer 1

Most of this is answered in another post, but I will not vote this as duplicate as you ask if the data will be "readable" or unencrypted at anytime during the process.

Regenerating the master key will re-create it and all other keys that it protects but will not create new certificates. As stated in the documentation:

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key."

So yes, during the key change operation the data will be unencrypted.

If you are using TDE you can rotate the certificate without re-encrypting the data as the Database Encryption Key (DEK) is stored in the database and is protected by a certificate in the master database so you can re-encrypt the DEK without having to re-encrypt the whole database just by creating a new certificate and alter the database encryption key

CREATE CERTIFICATE NEWCert 
    WITH Subject='NEW Certificate', 
    EXPIRY_DATE = 'SomeDate';

USE Database;

ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE NewCert;