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

  1. 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.

  2. 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
      


  3. 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
      


  4. 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

Published Tuesday, June 24, 2008 9:11 PM by sqldbatips
Filed under: , ,

Comments

# New in SQL 2008 : Transparent Data Encryption Part I - sqldbatips.com blog

Pingback from  New in SQL 2008 : Transparent Data Encryption Part I - sqldbatips.com blog

Tuesday, June 24, 2008 9:55 PM by sqldbatips.com blog

# Demo code from UK SQL 2008 Launch Event

The code for the demos from the UK SQL 2008 Launch Event are now available from the link below. Given

Wednesday, June 25, 2008 11:20 AM by SQL Server e dintorni

# Transparent Data Encryption

L’infrastruttura di cifratura basata su PKI è stata introdotta in SQL Server 2005 (a questo link trovate

Friday, June 27, 2008 5:34 PM by Data Recovery Reports

# New in SQL 2008 : Transparent Data Encryption Part II

[Source: SQL Server Community Blogs] quoted: 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