TDE test question

MF47 on Tue, 23 Oct 2012 07:59:45

Hello!

Please help me clarify this question on TDE encryption: http://www.lead2pass.com/downloadable/download/sample/sample_id/3292/ - question 8.

Or, in shot:

"You administer a Microsoft SQL Server 2012 database. You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

a) Create master key...
    Create certificate...

b) Backup certificate ... with private key...to \File

c) Create DEK

d) Alter Database Orders Set Encryption ON

You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. You need to be able to restore the database. Which Transact-SQL statement should you use before attempting the restore (options a-d)?

Correct answer  - C.

As far as I know DEK is contained in a database being encrypted and the only thing (except a master key) needed for the restoration of an encrypted db is a certificate wich was used during DEK creation.

Here is a good article on TDE: http://www.sqlmag.com/article/sql-server/transparent-data-encryption-142907

Listing 3 of this article answers exactly the same question:

http://www.windowsitpro.com/content1/topic/transparent-data-encryption-142907/catpath/sql-server/page/2

"To move or restore the database to another SQL Server instance, you need to restore the certificate to the new server instance. Listing 3 demonstrates how to move a backed up certificate to a new SQL Server instance."

Listing 3: Code to Move a Backed Up Certificate to a New SQL Server Instance

USE Master
 GO
    -- Create a new master key.
 CREATE MASTER KEY ENCRYPTION
 BY PASSWORD = 'MyNewStrongPassword'
   
-- Restore the certificate.
 
CREATE CERTIFICATE MySQLCert
 FROM FILE='c:\temp\MySQLCert'
 WITH PRIVATE KEY (
 FILE = 'c:\temp\MySQLCertKey',
 DECRYPTION BY PASSWORD='MyStrongPassword2')

So prior to restoring a TDE-encrypted database on another SQL instance we should 1) create a new master key 2) restore the certificate - why according to the correct answer C should we create ANOTHER DEK in the same database?

Thank you in advance,

Michael