It has been a while since I last blogged.  But as I stated before, I will not blog unless  I have issues finding a clear solution to an issue. Today I was trying to find out how to change TDE security at both the database level and cell level encryption when using a certificate. But first, do you know when your TDE encryption certificate is going to expire?  What are you doing to watch that certificate?

When you create TDE certificate by default, the certificate is just created for a year. Depending upon your auditor, you may be asked to change this TDE certificate yearly.  In this blog post, you can watch when your TDE certificate expires using policy-based management, https://www.mssqltips.com/sqlservertip/2388/internal-sql-server-certificates-have-expired-is-this-an-issue/.

I also change my TDE and Cell Level Encryption when I move and scrub the database for QA and Dev.  On the scrub server, I restore the production database with the production certificate.  After creating a QA Dev TDE Certificate, I change the restored production database to the QA Dev Tde Certificate.  Here is the script to make that change.

declare @EncryptionState int

— Change TDE Certificate

ALTER DATABASE ENCRYPTION KEY  

ENCRYPTION BY SERVER CERTIFICATE DEVQA_TDE_20170501;

select @EncryptionState = encryption_state

from sys.dm_database_encryption_keys

where db_Name(database_id) = ‘TdeDatabase’

–select @EncryptionState

while @EncryptionState <> 3

begin

WaitFor Delay ’00:01′

select @EncryptionState = encryption_state

from sys.dm_database_encryption_keys

where db_Name(database_id) = ‘TdeDatabase’

end;

/*

— Test to see it worked

Use TdeDatabase;

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘YourMasterPassword’;

select top 10 *

from people ;

close Master Key;

*/

— Create QA Dev Database Level Cert

Use TdeDatabase;

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘YourMasterPassword’;

If not exists(select name from sys.certificates where name = ‘TDE_DEV_QA_Field_Level_20170501’)

Begin

CREATE CERTIFICATE TDE_DEV_QA_Field_Level_20170501 WITH SUBJECT = ‘TDE DB Dev QA Field Level Certificate’, EXPIRY_DATE = ‘20170501’;

end;

close Master Key;

— Set the QA Encrypt Field

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘YourMasterPassword’;

OPEN SYMMETRIC KEY ProductEncryptField DECRYPTION BY CERTIFICATE Prod_Field_Level_20170501

— First, encrypt the key with a password.

–ALTER SYMMETRIC KEY ProductEncryptField

—    ADD ENCRYPTION BY PASSWORD = ‘YourPassword’;

— Now remove encryption by the certificate.

ALTER SYMMETRIC KEY ProductEncryptField

   DROP ENCRYPTION BY CERTIFICATE Prod_Field_Level_20170501;

ALTER SYMMETRIC KEY ProductEncryptField

   Add ENCRYPTION BY CERTIFICATE TDE_DEV_QA_Field_Level_20170501;

CLOSE SYMMETRIC KEY ProductEncryptField;

drop certificate Prod_Field_Level_20170501;

This is how you change both the TDE and Field Level Encryption Certificates.  Note, SQL Server 2016 Always Encryption is not addressed here.