What do I need to restore an encrypted MSSQL database?

Question

I am trying to get my head around MSSQL TDE encryption.

I have followed these steps to encrypt a database:

1) Create a master key on the master database:

USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='thepbpass';
GO

2) Create certificate:

CREATE CERTIFICATE mycert
WITH SUBJECT='mydb DEK';
GO

3) Associate the certificate to the database:

USE mydb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert;
GO

4) Encrypt the database:

ALTER DATABASE mydb
SET ENCRYPTION ON;
GO

5) Export the certificate.

BACKUP CERTIFICATE mycert TO FILE = 'path_to_file'  
GO

My question is:

If my server died and I needed to restore a backup, do I have everything required for the restore?

I have the following:

• A certificate stored in a secure location.
• Knowledge of the secure master key password.

Do I need to worry about private keys or public keys? I have seen discussion about private and public keys online and I am not sure how they are relevant to my configuration or how they are different from the master key which I created in step 1.

Answer 1

To put it simply, your mistake is in step 5. You need to backup the private key with the certificate. You will need to secure the password so you can use it to restore the certificate on the destination server.

It would be helpful for you to concentrate on learning more about the encryption hierarchy. The private key of the TDE certificate is the only key that can decrypt the database master key, and the DMK is a symmetric key that directly encrypts and decrypts pages in the TDE enabled database.

BACKUP CERTIFICATE mycert TO FILE = 'Path_to_file.cer'  
    WITH PRIVATE KEY ( FILE = 'path_to_file.pvk' ,   
    ENCRYPTION BY PASSWORD = 'password to encrypt the file path_to_file.pvk' );  
GO  

Answer 2

We can't restore a backup on a different instance without Certificates. We have to restore the Cert from the source server, then we should be able to restore the database from the backup.

Obviously your certificate & master key are required for restore.

As MSSQLTREK Blog here To restore the Certificate from source (and you have to create a DMK (Database Master Key) on destination if you don’t have one), the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s).

Create a Master Key (you don’t have to do this if you already have DMK) and create a certificate from the cert backup and provide private Key file and password to decrypt it.

Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.

For your further ref Here and Here

Note : If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. So there’s a high possibility impacting other databases which are actually not using TDE feature while you are in the process of enabling TDE!

Answer 3

Have you read this? https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/ I'm doing this from my phone so I cannot walk through this guide. It looks thorough and mentions a few things I had overlooked during my foray into TDE.

Of course there's the standard disclaimer: hopefully you're not doing this in production! The first time I set up TDE, I moved the database files off the server and decommissioned it. I did not realize I hadn't copied the keys. It was test, so no major loss, but if that had been production data I would've been out of a job for sure.