Restoring a TDE Encrypted Database

Restoring AdventureWorks2014

In order to restore the database, we'll need the following tokens:

- A certificate file

- A private key

- The Password for the private key

- The backup file

1. Let's start by obtaining the backup file (_.bak_). The company usually would provide this on a FTP server. Navigate to the WinSCP on the and download the backup to folder **C:\bi_backup\**. Since I’m doing it locally, I’ve already retrieved the file.

The database is TDE Encrypted. In order to test it – Try restoring it using the file and the following syntax:

RESTORE DATABASE AdventureWorks2014
FROM 
DISK = 'C:\FileStructure\bi_Projects\bi_backup\AdventureWorks2014'

We Get the following error:

2. After successfully retreiving the backup file the next step would be to locate the .cer and .pvk files. They are usually provided by the Company via encrypted email. After saving them locally on your machine please upload to a folder of your choosing.

The Company should also provide you with a password for the .pvk file.

3. Open SSMS and connect to the local server. On a new sheet, in the master database create a master key for the database.

USE master; 
GO 
-- CREATE MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1'; 
GO
-- Check if Database Master Key Exists
SELECT name
FROM sys.symmetric_keys
WHERE name LIKE '%DatabaseMasterKey%';

4. Next step is to create the certificate using the tokens that we've just saved in **C:\bi_certificates**.

CREATE CERTIFICATE MyTDECertificate -- Make sure the certificate is named the same!!!!!!
FROM FILE = 'C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.cer'
WITH PRIVATE KEY (FILE='C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.pvk', 
 DECRYPTION BY PASSWORD = 'Password1'); 
GO
-- this provides the list of certificates
SELECT * FROM sys.certificates

**NB! Make sure you always backup your certificates! C:\bi_certificates\Backup**

-- Create a backup of the server certificate in the master database. 
-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server 
-- ALWAYS BACKUP YOUR CERTIFICATES AND PRIVATE KEYS
BACKUP CERTIFICATE MyTDECertificate
TO FILE = 'C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.cer'
WITH PRIVATE KEY (
FILE='C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.pvk',
ENCRYPTION BY PASSWORD='Password1'
) 

5. Alter the database and set encryption to ON.

USE AdventureWorks2014; 
GO 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE MyTDECertificate; 
GO 

6. Restore the database using the following code.

DROP DATABASE IF EXISTS AdventureWorks2014;
RESTORE DATABASE AdventureWorks2014
FROM 
DISK = 'C:\FileStructure\bi_Projects\bi_backup\AdventureWorks2014' 

Summary

Make sure you've got all the tokens saved before you start:

- A certificate file

- A private key

- The Password for the private key

- The backup file

Common Errors and troubleshooting:

Msg 33111, Level 16, State 3, Line 1 Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

In order to resolve these, make sure you have the Database Master Key, Certificate and private key installed on the master database.

Msg 15208, Level 16, State 6, Line 21 The certificate, asymmetric key or private key file is not valid or does not exist; or you do not have permissions for it.

Double-check the NTFS permissions for the C:\bi_certificates folder for users:

MSSQLSERVERNETWORK SERVICE

Things to be careful of:

1. Remember that the certificate expires after 1 year!

2. After you first create it on the database make sure you set a reminder to request a new certificate before it expires to avoid disaster and data loss.

3. When creating the certificate, make sure it has the same name as the given certificate name at the time of creation.

4. Check the edition of your SQL Server - Standard edition doesn't support TDE. Only Developer and Enterprise edition do.