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.