TDE test question

Category: sql server documentation

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





Replies

Maggie Luo on Tue, 23 Oct 2012 16:47:15


Hi MF,

I think restore a TDE-encrypted database on another SQL instance we should 1) create a new master key 2) restore the certificate. This is the right steps and I have tested in my server.

Create DMK should be used before attempt to restore the database.

TechNet Subscriber Support
If you are
TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Thanks.

MF47 on Wed, 24 Oct 2012 06:24:39


Hi Maggie,

thank you for your reply!

"Create DMK should be used before attempt to restore the database." - "DMK" - you mean DEK (Database encryption key)? Why?...The database is already encrypted...Why should I create one more DEK???

Regards,

Michael

Maggie Luo on Wed, 24 Oct 2012 06:35:34


Hi MF,

DMK means database master key not database encryption key. The database is already encrypted. You need not to create one more DEK.


Thanks.

MF47 on Fri, 02 Nov 2012 12:46:20


Maggie, thank you!

I voted for option A too (create master key), but it means this answer (http://www.lead2pass.com/downloadable/download/sample/sample_id/3292/ - question 8) is not correct. Should it be a real exam question what answer should an applicant choose...???

Best regards,

Michael


MF47 on Fri, 09 Nov 2012 08:15:56


P.S. "I think restore a TDE-encrypted database on another SQL instance we should 1) create a new master key 2) restore the certificate." - but there're no such answer option: option A says: "Create a master key and Create a certificate (not RESTORE a certificate!)."

This question has no correct answer because it's not possible to restore a TDE-encrypted db using EXACTLY the same set of procedures as were used during the db encryption!

Best regards,

Michael