![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\")
<\/p>\n<\/p>\n
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:<\/p>\n
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.<\/p>\n
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.<\/p>\n
| \n Sunday<\/p>\n<\/td>\n | \n Monday<\/p>\n<\/td>\n | \n Tuesday<\/p>\n<\/td>\n | \n Wednesday<\/p>\n<\/td>\n | \n Thursday<\/p>\n<\/td>\n | \n Friday<\/p>\n<\/td>\n | \n Saturday<\/p>\n<\/td>\n<\/tr>\n |
| \n FULL<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n | \n LOG<\/p>\n<\/td>\n<\/tr>\n |
| \n TDE_Cert1<\/p>\n<\/td>\n | \n TDE_Cert2<\/p>\n<\/td>\n | \n TDE_Cert3<\/p>\n<\/td>\n | \n \u2026<\/p>\n<\/td>\n | \n \u2026<\/p>\n<\/td>\n | \n \u2026<\/p>\n<\/td>\n | \n \u2026<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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?<\/p>\n To answer, let\u2019s try with the AdventureWorks2012 database and the table Person.Person.<\/p>\n First we can see the current server certificate used to protect the DEK of the AdventureWorks2012 database (we can correlate with the certificate thumbprint) :<\/p>\n SELECT<\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name AS certificate_name,<\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pvt_key_encryption_type_desc AS pvt_key_encryption,<\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 thumbprint<\/span><\/span><\/div>\n FROM master.sys.certificates <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n WHERE name LIKE ‘TDE_Cert%’;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n
SELECT<\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DB_NAME(database_id) AS database_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key_algorithm,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key_length,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 encryptor_type,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 encryptor_thumbprint<\/span><\/span><\/div>\n FROM sys.dm_database_encryption_keys<\/span><\/span><\/span><\/span><\/span><\/div>\n WHERE database_id = DB_ID(‘AdventureWorks2012’)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
Now we perform a full backup of the AdventureWorks2012 database following by the database log backup:<\/p>\n BACKUP DATABASE [AdventureWorks2012]<\/span><\/span><\/span><\/span><\/div>\n TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.BAK’<\/span><\/span><\/span><\/span><\/span><\/div>\n WITH INIT, STATS = 10;<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0<\/span><\/div>\n BACKUP LOG [AdventureWorks2012]<\/span><\/span><\/span><\/span><\/div>\n TO DISK = ‘E:SQLSERVERENCRYPTEDBACKUPADVENTUREWORKS2012_DB.TRN’<\/span><\/span><\/span><\/span><\/span><\/div>\n WITH INIT, STATS = 10;<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
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<\/p>\n — Create a new server certificate<\/span><\/div>\n USE [master];<\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n \u00a0<\/span><\/div>\n CREATE CERTIFICATE TDE_Cert2<\/span><\/span><\/span><\/span><\/div>\n WITH SUBJECT = ‘TDE Certificat 2’;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0<\/span><\/div>\n — Encrypt the DEK by the new server certificate TDE_Cert_2<\/span><\/div>\n USE AdventureWorks2012;<\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n \u00a0<\/span><\/div>\n ALTER DATABASE ENCRYPTION KEY <\/span><\/span><\/span><\/span><\/span><\/div>\n ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2;<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n — Drop the old server certificate TDE_Cert <\/span><\/div>\n USE [master];<\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n \u00a0<\/span><\/div>\n DROP CERTIFICATE TDE_Cert;<\/span><\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n \u00a0<\/span><\/div>\n SELECT<\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name AS certificate_name,<\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pvt_key_encryption_type_desc AS pvt_key_encryption,<\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 thumbprint<\/span><\/span><\/div>\n FROM master.sys.certificates <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n WHERE name LIKE ‘TDE_Cert%’;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n GO<\/span><\/div>\n
SELECT<\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DB_NAME(database_id) AS database_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key_algorithm,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key_length,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 encryptor_type,<\/span><\/span><\/span><\/div>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 encryptor_thumbprint<\/span><\/span><\/div>\n |
![\"billet5_tde_certificate_1\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet5_tde_certificate_1.jpg\")
<\/p>\n![\"billet5_tde_dek_1\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet5_tde_dek_1.jpg\")
<\/p>\n![\"billet5_tde_bckp_1\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet5_tde_bckp_1.jpg\")
<\/p>\n![\"billet5_tde_dek_2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet5_tde_dek_2.jpg\")
<\/p>\n