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