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

Actions

Information

One response

5 04 2014
sms en masse gratuit

It’s really a nice and useful piece of info. I am happy that you just shasred this useful info with us.
Please stay us up too dae like this. Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: