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) :
Now we perform a full backup of the AdventureWorks2012 database following by the database log backup:
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
We perform again a new backup log:
Finally we repeat the same steps as above a last time (rotate the server certificate and perform a new log backup) :
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:
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 /!)
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:
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:
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:
…
Now we begin our restore process by trying to restore the concerned page from the full backup but we encounter the first problem:
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:
CREATE CERTIFICATE TDE_Cert
Then if we try to restore the page from the full database backup it works now:
To continue with the restore process we have now to restore the transaction log backup sequence with beginning with the ADVENTUREWORKS2012_DB.TRN media:
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:
Ok we have to restore the TDE_Cert_2 certificate …
… And we retry to restore the second transaction log. As expected it works:
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.
The restore process is now finished and we can now reading data from the Person.Person table without problem:
…
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