Annual change to encryption certificates and key - How to

i have been tasked change encryption keys greatest extent possible without decrypting , re-encrypting existing column values.  i realize believe should not required, unless comprise suspected, have nonetheless been tasked it.  we use column-level encryption , we're running sql 2014 standard edition.

certificates , key created, , used shown code below, names , other unique string changed.

references changing key address tde, , not in use.  i understand change in symmetric key require decryption current key , re-encryption new key, , can regenerate database master key used encrypt certname certificate.  however, looking @ values in sys.certificates, not appear certname certificate affected.

guess question is, regeneration of database master key cause re-encryption of certificate in database in way not apparent, , symmetric key encrypted re-encrypted?  if not, steps can take rotate certificates , key in way not require decryption , re-encryption of existing data?

in advance advice ou can offer.


creation:

create master key encryption password = 'strongpassword';

create certificate certname
subject = 'certificate encrypt , decrypt';

create symmetric key keyname
algorithm = aes_256,
key_source = 'long sentence',
identity_value = 'long sentence'
encryption certificate certname;

open keyname decription certificate certname;

usage:

encryptbykey(key_guid('keyname'), @email_addr)

select convert(varchar(250), decryptbykeyautocert(cert_id('certname'), null, email_addr))

hi byron,

according knowledge,  regeneration of database master key recreate certificates protects. certificate first decrypted old master key, , encrypted new master key. in addition, regeneration of database master key won’t cause decryption , re-encryption of symmetric key since symmetric key encrypted certificate, not database master key.

for certificate rotation without decryption , re-encryption of existing data, perform following steps example.

1.open symmetric key 'keyname' following statement.

open symmetric key keyname decryption certificate certname

2.create temporary certificate 'certtmp' , encrypt key 'keyname' following statement.

create certificate certtmp subject = 'temporary certificate protecting keyname'

alter symmetric key keyname add encryption certificate certtmp

3.drop old encryption certificate 'certname' , certificate 'certname' following statement.

alter symmetric key keyname drop encryption certificate certname

drop certificate certname

4.create new certificate old certificate name 'certname' following statement.

create certificate certname subject = 'certificate protecting keyname of version 2'

alter symmetric key keyname add encryption certificate certname

5.drop encryption certificate 'certtmp' , certificate 'certtmp' following statement.

alter symmetric key keyname drop encryption certificate certtmp

drop certificate certtmp

6.close opened keys following statement.

close symmetric keys

regards,
michelle li

SQL Server  >  SQL Server Security