SQL Server 2008 R2 Disaster Recovery After Transparent Data Encryption (TDE) Implementation Re-Encrypt Master Key

16 07 2013

Being new to TDE I decided that my first task after encrypting all the data is to test restoring the data. First I restored the Service Master Key and the Master Key without issue. When I attempted to restoer the Certificate I recieved an error:

“Please create a master key in the database or open the master key in the session before performing this operation”
Microsoft SQL Server, Error: 15581

A quick google search revealed that I needed to issue the following command

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'

After accomplishing this I was able to restore the certificate to the server.

When I attempted to restore, using the GUI, the first database again I recieved the same error previously encountered. I ran the command to open the master key again to no avail.

Next I performed a SQL Command restore

USE master
RESTORE DATABASE dbname
FROM DISK = ''
WITH RECOVERY,
MOVE '' TO '',
MOVE '' TO ''
GO

This approach did restore the database and I was able to select data out of it.

This query was then ran to determine the encryption status of the restored database:

SELECT sys.databases.name, sys.dm_database_encryption_keys.encryption_state FROM sys.dm_database_encryption_keys 
JOIN sys.databases ON sys.dm_database_encryption_keys.database_id = sys.databases.database_id

The result was a little disapointing as it did not list the database as being encrypted, and I was still unable to use the GUI to perform restores!

As it turns out the Master key nust be re-encrypted by the service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
CLOSE MASTER KEY
GO

Once these commands were issued I was able to restore again from the GUI and the databases restored, by either SQL or GUI, restored encrypted.

Apparently once the master key is re-encrypted after a restore it will automatically be opened as opposed to manually having to be opened. It appears the msater key should be re-encrypted prior to restoring the certificate.

Advertisements




SQL Server 2008 R2 – Implementing Transparent Data Encryption (TDE)

12 07 2013

I struggled through figuring out the ins and outs of implementing TDE in SQL Server 2008 R2 and decided it would be nice to post this guide to help poeple new to TDE get their databases encrypted!
It is importnat to mention more than once that by implementing TDE it is extremely important to take backups of all the keys and certificates created to help endusre that no data is lost. Failure to perform backups of these items is almost a gurantee that you will lose data, it will be rendered unrecoverable. you have been warned!

This scripts prepares your SQL Server to encrypt datatabases

USE master
GO

BACKUP SERVICE MASTER KEY TO FILE = '<path and file name to backup use .bak>' ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<STRONG PASSWORD>';
GO

BACKUP MASTER KEY TO FILE = '<path and file name to backup use .bak>' ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>'
GO

CREATE CERTIFICATE <Certificate Name> WITH SUBJECT = '<Server Name> DEK Certificate';
GO


BACKUP CERTIFICATE <Certificate Name> TO FILE = '<path and file name to backup use .cer>' WITH PRIVATE KEY (FILE = '<path and file name to backup use .pvk>', ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>')
GO

Now to encrypt the database

USE <database to encrypt>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <Certificate Name>;
GO
ALTER DATABASE <database to encrypt>
SET ENCRYPTION ON;
GO

And now we can validate that the database is indeed encrypted or in the process of using this command

SELECT sys.databases.name, sys.dm_database_encryption_keys.encryption_state FROM sys.dm_database_encryption_keys 
JOIN sys.databases ON sys.dm_database_encryption_keys.database_id = sys.databases.database_id

* Status of 1 indicates that the database is not encrypted
* Status of 2 indicates that the database is currently performing encryption of the database
* Status of 3 indicates that the database is encrypted

An Actual example:

USE master
GO
BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key_07-12-13.bak' ENCRYPTION BY PASSWORD = 'P@$$w0rd!!'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd!!2';
GO
BACKUP MASTER KEY TO FILE = 'c:\master_key_07-12-13.bak' ENCRYPTION BY PASSWORD = 'P@$$w0rd!!3'
GO
CREATE CERTIFICATE encCer WITH SUBJECT = 'Database DEK Certificate';
GO
BACKUP CERTIFICATE encCer TO FILE = 'c:\enCer_certificate_07-12-13.cer' WITH PRIVATE KEY (FILE = 'c:\private_key_07-12-13.pvk', ENCRYPTION BY PASSWORD = 'P@$$w0rd!!4')
GO
USE test
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE encCer;
GO
ALTER DATABASE test
SET ENCRYPTION ON;
GO

Helpful Tips

1. By implementing TDE and not following these guidelines will result in data loss.

2. Give meaningful names to your backup files.

3. Use different complex passwords for each key, certificate, and recovery of these items.

4. Store all your passwords and backup files in a secure location

5. Keep multiple copies of the passwords and backup files.

6. I keep all my passwords in an encrypted file on my computer, a backup of this on tape, and on a printed out hardcopy located in the safe!

7. keep a copy of the backed up keys and certificate on the local server, on a network share, and on tape!

8. No this is not overkill!