Recover TDE Databases in Disaster : Recover Steps for 3 Common Scenarios

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Summary: This article discusses 3 common scenarios where you can and cannot recover your TDE-enabled database that using native SQL certificate stored on master database to encrypt the TDE (not using AKV or 3rd-party EKM/HSM). 

 

In unexpected scenarios, you may encounter challenges in recovering your TDE-enabled databases. For example, you have lost the backup files for your TDE certificate and your master database is corrupted. You only have backup files for a few remaining components of your TDE databases and you need to save your production database immediately.

 

The key point in successfully restoring a TDE-enabled database is the ability to fix the encryption hierarchy by recovering all dots on the chain (i.e., machine account, service master key, master key of master database, certificate, database encryption key). Below is the encryption hierarchy when we use native SQL Server certificate stored on master database for TDE database. We will discuss the AKV and 3rd party EKM/HSM scenario in another blog.

EstherXinMSFT_1-1636906199951.png

Below are 3 common scenarios where DEK is encrypted by certificate stored on master database. In these situations, we are missing part of the important files on encryption hierarchy to restore the chain.  We'd like to address the importance of keeping valid backups of these files in case of disaster:

1) certificate that encrypts the DEK;

2) password to the private key of the TDE certificate;

3) password to decrypt the master key in master database;

4) healthy backup of system and user databases. 

 

Note: Please ensure we have valid backup for all related files before you make any changes as discussed below. Below 3 scenarios are just testing results in our lab. You may have unexpected scenario in your PRODUCTION environment that caused by local issue. 

 

SCENARIO 1 

------------------ 

 

We don’t have:  

 

  1. Password to decrypt the master key on the master database. 
  2. Backup of the master database. 

 

We have below valid and healthy files without corruption: 

  1. Backup of the certificate and private key. 
  2. Password to the private key of the certificate to be restored. 
  3. Backup of the TDE database. 

 

Steps

 

  1. Restore the certificate to the available instance. 

 

CREATE CERTIFICATE TDE_Test_Cert5 FROM FILE = 'c:\temp\TDE_Test_Cert5' 

WITH PRIVATE KEY (FILE = 'c:\temp\TDE_Test_Cert5_Key' , 

DECRYPTION BY PASSWORD = 'TDE_Cert_Backup_password123!!!');   

 

     2. Restore the TDE database  

 

RESTORE DATABASE [DB_TDE_Test]  

FROM  DISK = N'\\sql2016n1\C$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_full.bak'  

WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 

 

 

SCENARIO 2 

------------------- 

 

We don’t have:  

 

  1. Backup of the certificate and private key. 
  2. Password to the private key of the certificate to be restored. 

 

We have below valid and healthy files without corruption: 

  1. Password to decrypt the master key of the master database. 
  2. Backup of the master database. 
  3. Backup of the TDE database. 

 

 

Steps

 

 

  1. Restore master database to another SQL Server instance with the same CU level using *.BAK file to start the instance.  

 

<With backup of master database > 

 

  1. Open CMD as admin 
  2. Start this SQL Server instance in single user mode 

 

net start MSSQL$INSTANCENAME /f /mSQLCMD /T3608 

 

 

     3. Connect to the instance as single user mode using SQLCMD 

 

SQLCMD -SSERVERNAME\INSTANCENAME -E 

 

     4. Restore the master database with replace 

 

  

           Restore database master from disk = 'C:\Users\Administrator.YIXIN12\Desktop\backup copy\master.bak' with replace 

           Go 

 

     5. Once master database is successfully restored, the service will be terminated.  

     6.  If all the system DB path is the same on this instance, you do not have to modify the physical path. Otherwise , you have to use SQLCMD single user mode to modify the path stored on master database for system databases before you start the service in regular mode.  

 

     7. After the master database and system database configuration has been successfully set up, open the master key of the master database using the password. 

 

 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'verycomplicateDMKpassword1234!!!New' 

GO 

 

--you can check if the DMK in the master database is now encrypted by the SMK 

  

select is_master_key_encrypted_by_server, * from sys.databases 

where database_id = db_id(N'master') 

 

     8. Encrypt the master key with the current service master key.  

 

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

GO 

 

     9. In this way, the certificate is successfully decrypted and encrypted by the master key on this new environment, You can then restore the TDE database. 

 

RESTORE DATABASE [DB_TDE_Test]  

FROM  DISK = N'\\sql2016n1\C$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_full.bak'  

WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 

 

 

SCENARIO 3  

----------------- 

 

We don’t have:  

 

  1. Password to decrypt the master key on the master database. 
  2. Backup of the certificate and private key 

We have below valid and healthy files without corruption: 

  1. Backup of master database. 
  2. Backup of the TDE database.  
  3. (If you don’t have this condition, you can start on step 4) The old service account can be used in another instance in the same CU level on another machine on the same domain. 

 

 

Steps

 

 

  1. Restore master database to another SQL Server instance on the same domain where SQL Server is on the same CU version and ensure we use the same service account for this SQL Server instance. If you cannot use the same service account,after you have restored the master database, please go to “step 4”. 

 

<With backup of master database > 

      1) Open CMD as admin 

      2) Start this SQL Server instance in single user mode 

 

net start MSSQL$INSTANCENAME /f /mSQLCMD /T3608 

 

      3) Connect to the instance as single user mode using SQLCMD 

 

SQLCMD -SSERVERNAME\INSTANCENAME -E 

 

      4) Restore the master database with replace 

 

           Restore database master from disk = 'C:\Users\Administrator.YIXIN12\Desktop\backup copy\master.bak' with replace 

           Go 

 

      5) Once master database is successfully restored, the service will be terminated.  

      6) If all the system DB path is the same on this instance, you do not have to modify the physical path. Otherwise , you have to use SQLCMD single user mode to modify the path stored on master database for system databases before you start the service in regular mode.  

 

 

    2.  If step 1 is successful and the restored master database has no corruption, the master key would be already encrypted by the service master key and thus no need to decrypt the master key in master DB again. In this situation, the certificate is also accessible. We can restore TDE database. If you encounter error in this step, please go to “step 4”. 

 

RESTORE DATABASE [DB_TDE_Test]  

FROM  DISK = N'\\sql2016n1\C$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DB_TDE_Test_full.bak'  

WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 

 

       3.  If step 2 is successful, we need to reboot the server to get the Service Master Key to be encrypted by the current machine account. Otherwise, you cannot take backup of the TDE certificate and will encounter below error. This is because before rebooting the server, the service master key is encrypted by the incorrect machine account (original machine account) rather than the current one. 

 

                              Msg 15151, Level 16, State 1, Line 1 

                              Cannot find the certificate 'TDE_Test_Cert5', because it does not exist or you do not have permission. 

 

        4. If you encounter error for master key is not encrypted (the restored master database has corruption) or does not exist in step 2 or if you cannot re-use the same service account for this new SQL Server instance, you will need to first decrypt the master key in the restored master database and get it re-encrypted by the current service master key in the new environment. Since you don’t have the password to decrypt the master key, you need to first try to regenerate the master key with a new password.  

 

USE master 

GO 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'yournewpassword'; 

GO 

 

          5.  (Please consider all related risks and take valid backup of all data before you make any change as discussed in this step). If the master key cannot be regenerated and encounters error such as below, you can test to use “FORCE REGENERATE” option. However, there are risks that you will lose data encrypted by this master key if some of the certificates and credentials encrypted by this master key cannot be decrypted during the “FORCE REGENERATE” operation. This is because whenever a certificate or credential cannot be decrypted during the “FORCE REGENERATION” option process, this encrypted data will be skipped and remain in encrypted status forever. Thus, you will have data loss. 

 

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost. 

 

 

USE master 

GO 

ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; 

GO 

 

           6. Then let the master key be encrypted by the new service master key on this instance so you don’t have to open the master key every time you want to access decrypted data protected by this master key 

 

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

 

          7. If step 5 successfully decrypts the certificate, you can successfully restore the TDE database. Please reboot the server to get the Service Master Key to be encrypted by the current machine account. 

 

         8.  If step 5 succeeds but the certificate fails to be decrypted during the process, you will encounter error as below when you try to restore the TDE database as the certificate cannot be decrypted by the master key in the master database and thus the DEK cannot be decrypted. In this case, you lose the data encrypted by this certificate and thus lose the data encrypted by the TDE database encryption key (DEK). Unless you have a valid backup of the certificate and private key (and the password to decrypt the private key) as that in scenario 2 or the correct password to open the master key of the master database as that in scenario 1, you will lose data on this TDE database. 

 

EstherXinMSFT_0-1636905069111.jpeg

 

 

 

Reference: Troubleshooting DMVs

=======================

 

 

  1. Check if the DMK exist in master database

 

USE master

GO

 

SELECT * FROM

sys.symmetric_keys

WHERE name = '##MS_DatabaseMasterKey##'

 

  1. Check if the certificate is created in master database. A certificate is equivalent to an asymmetric key

 

USE master

GO

 

select * from sys.certificates

 

  1. Check if the database is encrypted/progress (encryption_state = 3 encrypted; =2 in progress)

 

 

USE master

GO

SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint , dek.*     FROM sys.dm_database_encryption_keys dek     INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint

 

  1. Check if the DMK in the master database is now encrypted by the SMK (is_master_key_encrypted_by_server = 1)

 

select is_master_key_encrypted_by_server, * from sys.databases

where database_id = db_id(N'master')

 

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.