New in SQL 2008 : Transparent Data Encryption Part II
In Part I we created the required keys and certificate to enable Transparent Data Encryption for the TDE database. In this post I'll cover how to restore an encrypted database to another instance (e.g. to set up database mirroring or log shipping). In order to be able to restore the now encrypted backup of the TDE database, we need to create the server certificate used to secure the Database Encryption Key for the TDE database (DEKCert). In order to do this we need the backup files we created in Step 4 of Part I.
Example Walkthrough
-
First we will try and restore the TDE database to another instance without first creating the server certificate. As you will see below, this fails because the server certificate used to secure the database encryption key cannot be found on the new instance.
-- backup database if not already done
BACKUP DATABASE TDE
TO DISK = 'c:\TDE_Encrypted.bak' WITH init
GO
-- try and restore to a different instance
RESTORE DATABASE TDE
FROM DISK = 'c:\TDE_Encrypted.bak'
WITH move 'TDE' TO 'c:\TDE.mdf',
move 'TDE_log' TO 'c:\TDE.ldf'
GO
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xCA741797B81ED8D1305EAE65A747BA51E1DAB80D'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-
On our new instance, we need to create the server certificate used to secure our database encryption key for the TDE database (DEKCert). We do this by creating a certificate from the backups we created in Step 4 of Part I (if you don't have the backups simply run the backup certificate part again on the original instance). We need to create the database master key for the master database as in Part I if it doesn't exist before we can create the certificate.
USE master
GO
-- create database master key if it doesn't exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sdfsdfysd092735kjn$&adsg'
GO
--create TDE certificate from backup
CREATE CERTIFICATE DEKCert
FROM FILE = 'c:\DEKCert'
WITH PRIVATE KEY (FILE = 'c:\DEKCertPrivKey',
DECRYPTION BY PASSWORD = 'sd092rhhh4735kjn$&adsg')
GO
-
Now that the server certificate used to encrypt the database encryption key for the TDE database is present on the new instance, we can successfully restore the TDE database.
RESTORE DATABASE TDE
FROM DISK = 'c:\TDE_Encrypted.bak'
WITH move 'TDE' TO 'c:\TDE.mdf',
move 'TDE_log' TO 'c:\TDE.ldf',
recovery
GO
-
Just to make sure, you can query the test table to make sure you can see the data on the new instance
SELECT * FROM TDE.dbo.t1
You can find more details about Transparent Data Encryption in the SQL Server 2008 Books Online. I haven't had a chance to do any performance test of this feature yet but it will be interesting to see what the impact is both for the encrypted database and the instance as a whole (due to tempdb being automatically encrypted)
The sample code for this example is attached to this post