Transparent Data Encryption requires the creation of a database key encryption. The database key is a part of the hierarchy of SQL Server encryption tree with at the top of the tree the DPAPI. Then if we traverse the tree from the top to bottom we can find the service master key, the database master key, the server certificate or the asymmetric key and finally the database encryption key (AKA the DEK). In this hierarchy each encryption key is protected by its parent. Encryption key management is one of the toughest tasks in cryptography. Managing improperly the encryption keys can compromises the entire security strategy. Here the basis of encryption key:

  • Limit encryption key access to only those who really need it
  • Backup encryption keys and secure them. This is important we can restore them in case of corruption or disaster recovery scenarios
  • Rotate the encryption keys on regular basis. Key rotation based on a regular schedule should be part of the IT policy. Leave the same encryption key in place for lengthy periods of time give hackers and other malicious persons the time to attack it. By rotating your keys regularly your keys become a moving target, much harder to hit.

SQL Server uses the ANSI X.917 hierarchical model for key management which has certain advantages over a flat single-model for encryption keys, particularly in terms of key rotation. With SQL Server, rotate the encryption key that protects the database encryption key requires decrypting and reencrypting an insignificantly small amount of symmetric key data and not the entire database.

However manage the rotate of the encryption key is very important. Imagine a scenario with a schedule rotate every day (yes we are paranoid!!!) and you have a strategy backup with a full back up every Sunday and a transaction log backup every night between Monday and Sunday.

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

FULL

LOG

LOG

LOG

LOG

LOG

LOG

TDE_Cert1

TDE_Cert2

TDE_Cert3

Here an interesting question I had to answer: If I have a database page corruption on Thuesday morning that requires a restore of the concerned page from the full backup and the couple of backup logs from Monday to Tuesday does it work with only the third encryption key? In others do I need all the certificates TDE_Cert1, TDE_Cert2 and TDE_Cert3 in this case?

To answer, let’s try with the AdventureWorks2012 database and the table Person.Person.

First we can see the current server certificate used to protect the DEK of the AdventureWorks2012 database (we can correlate with the certificate thumbprint) :

SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

billet5_tde_certificate_1

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

billet5_tde_dek_1

Now we perform a full backup of the AdventureWorks2012 database following by the database log backup:

BACKUP DATABASE [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.BAK’
WITH INIT, STATS = 10;
 
BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.TRN’
WITH INIT, STATS = 10;

billet5_tde_bckp_1

Then according to our rotate strategy we change the old server certificate TDE_Cert by the new one TDE_Cert_2 to protect the DEK

— Create a new server certificate
USE [master];
GO
 
CREATE CERTIFICATE TDE_Cert2
WITH SUBJECT = ‘TDE Certificat 2’;
 
— Encrypt the DEK by the new server certificate TDE_Cert_2
USE AdventureWorks2012;
GO
 
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2;
GO
— Drop the old server certificate TDE_Cert
USE [master];
GO
 
DROP CERTIFICATE TDE_Cert;
GO
 
SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

billet5_tde_dek_2

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

billet5_tde_certificate_2

We perform again a new backup log:

BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_2.TRN’
WITH INIT, STATS = 10;

billet5_tde_bckp_2

Finally we repeat the same steps as above a last time (rotate the server certificate and perform a new log backup) :

— Create a new server certificate
USE [master];
GO
 
CREATE CERTIFICATE TDE_Cert3
WITH SUBJECT = ‘TDE Certificat 3’;
 
— Encrypt the DEK by the new server certificate TDE_Cert_3
USE AdventureWorks2012;
GO
 
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_3;
GO
 
— Drop the old server certificate TDE_Cert
USE [master];
GO
 
DROP CERTIFICATE TDE_Cert_2;
GO
 
SELECT
       name AS certificate_name,
       pvt_key_encryption_type_desc AS pvt_key_encryption,
       thumbprint
FROM master.sys.certificates
WHERE name LIKE ‘TDE_Cert%’;
GO

billet5_tde_certificate_3

SELECT
       DB_NAME(database_id) AS database_name,
       key_algorithm,
       key_length,
       encryptor_type,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID(‘AdventureWorks2012’)

billet5_tde_dek_3

BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_3.TRN’
WITH INIT, STATS = 10;

billet5_tde_bckp_3

So, we have achieved our backup strategy with a full backup and a sequence of 3 transaction logs backups before to initiate next a database corruption. In the same time we have perform the rotate of 3 server certificates as encryption keys. Now it’s time to corrupt a data page that belongs to the table Person.Person into the AdventureWorks2012 database:

— First we check IAM page to get a page ID that belongs to the Person.Person table
DBCC IND(AdventureWorks2012, ‘Person.Person’, 1);
GO

billet5_tde_dbcc_ind_person_person

Then we take randomly page from the result with the ID 2840. Then to corrupt quickly the page we use the undocumented DBCC WRITEPAGE as following (/! Don’t use DBCC WRITEPAGE in production environment /!)

ALTER DATABASE AdventureWorks2012 SET SINGLE_USER;
GO
 
DBCC WRITEPAGE(AdventureWorks2012, 1, 2840, 0, 2, 0x1111, 1);
GO
 
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO

 

We corrupt the page with ID 2840 by introducing at the offset 0 two bytes with a global value of 0x1111. The last directORBufferpool option allows page checksum failures to be simulated by bypassing the bufferpool and flush directly the concerned page to the disk. We have to switch the AdventureWorks2012 database in the single user mode in order to use this option.

No let’s trying to get data from the Person.Person table:

USE AdventureWorks2012;
GO
 
SELECT * FROM Person.Person;
GO

As expected a logical consistency I/O error with an incorrect checksum occurs during the reading of the Person.Person table with the following message:

billet5_tde_error_consistency

At this point we had two options:

  • Trying to run DBCC CHECKDB and the REPAIR option but we can likely loss data in this case
  • Restore the page ID 2840 from a consistent full back up and the necessary sequence of transaction log backups after taking a tail log backup

We are reasonable and we decide to restore the page 2840 from the necessary backups but first we have to take a tail log backup:

USE [master];
GO
 
— tail log backup
BACKUP LOG [AdventureWorks2012]
TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_TAILLOG.TRN’
WITH NORECOVERY, INIT, STATS = 10;

Now we begin our restore process by trying to restore the concerned page from the full backup but we encounter the first problem:

— Restore the page ID 2840 from the full backup
RESTORE DATABAE AdventureWorks2012
PAGE = ‘1:2840’
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.BAK’
WITH NORECOVERY, STATS = 10;
GO

billet5_tde_restore_page_full_backup_error

 

According to the above error message we can’t restore the page from this full backup media because it is protected by a server certificate. The displayed thumbprint corresponds to the TDE_Cert certificate which has been deleted during the rotate operation. At this point we can understand why it is important to have a backup of the server certificate stored somewhere. We can remember here the basis of encryption and key management.

Of course we are safe and we performed a backup of each server certificate after their creation and thus we can restore the server certificate TDE_Cert:

USE [master];
GO

CREATE CERTIFICATE TDE_Cert

FROM FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert.cer’
WITH PRIVATE KEY
(
       FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert.pvk’,
       DECRYPTION BY PASSWORD = ‘P@$$w0rd’
);
GO

 

Then if we try to restore the page from the full database backup it works now:

billet5_tde_restore_page_full_backup_success

To continue with the restore process we have now to restore the transaction log backup sequence with beginning with the ADVENTUREWORKS2012_DB.TRN media:

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.TRN’
WITH NORECOVERY, STATS = 10;
GO

 

billet5_tde_restore_page_full_backup_success

 

Then we try to restore the second transaction log backup ADVENTUREWORKS2012_DB_2.TRN and we face to the same problem as the earlier full backup. To open the backup media we have before to restore the certificate with the thumbprint displayed below:

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_2.TRN’
WITH NORECOVERY, STATS = 10;
GO

 

billet5_tde_restore_page_tran_log_backup_1_success

Ok we have to restore the TDE_Cert_2 certificate …

CREATE CERTIFICATE TDE_Cert_2
FROM FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert_2.cer’
WITH PRIVATE KEY
(
       FILE = ‘E:SQLSERVERENCRYPTEDBACKUPTDE_Cert_2.pvk’,
       DECRYPTION BY PASSWORD = ‘P@$$w0rd’
);
GO

 

… And we retry to restore the second transaction log. As expected it works:

billet5_tde_restore_page_tran_log_backup_2_success

At this point, we have only two transaction log backups to restore: ADVENTUREWORKS2012_DB_3.TRN and the tail log backup ADVENTUREWORKS2012_DB_TAILLO.TRN. Fortunately, these last two backup Medias are encrypted by the TDE_Cert_3 which is the current server certificate that protects the DEK.

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_3.TRN’
WITH NORECOVERY, STATS = 10;
GO

 

billet5_tde_restore_page_tran_log_backup_3_success

RESTORE LOG [AdventureWorks2012]
FROM DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB_TAILLOG.TRN’
WITH RECOVERY, STATS = 10;
GO

 

billet5_tde_restore_page_tran_log_backup_4_success

The restore process is now finished and we can now reading data from the Person.Person table without problem:

USE AdventureWorks2012;
GO
 
SELECT * FROM Person.Person

billet5_tde_select_person_person_table

To summarize, in this post we have seen the importance of a good key management with the backup / restore strategy. Of course we took a paranoid scenario to highlight quickly the problem but you can transpose easily the same in a normal context with a fair rotate schedule of the encryptions keys either if it is a server certificate, an asymmetric key or a third party tool. And you, how do you manage your backup strategy with the rotate of encryption keys?

By David Barbarin