August 11, 2011 at 8:38 am
Steve,
You ask if a copy of the database encryption key is needed to restore a backup and then you answer that a copy of the server certificate used to create the key is needed to restore the database. This incorrect answer is based on conflating the DEK with the certificate used to encrypt the DEK.
Check out the CREATE DATABASE ENCRYPTION KEY syntax at http://technet.microsoft.com/en-us/library/bb677241.aspx.
According to TechNet:
"The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key."
And making the distinction even clearer:
"The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database."
August 11, 2011 at 8:52 am
tbailey 19088 (8/11/2011)
Steve,You ask if a copy of the database encryption key is needed to restore a backup and then you answer that a copy of the server certificate used to create the key is needed to restore the database. This incorrect answer is based on conflating the DEK with the certificate used to encrypt the DEK.
Check out the CREATE DATABASE ENCRYPTION KEY syntax at http://technet.microsoft.com/en-us/library/bb677241.aspx.
According to TechNet:
"The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key."
And making the distinction even clearer:
"The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database."
My understanding of things was the same based on my studies tbailey 19088... :unsure:
August 11, 2011 at 9:26 am
nice question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2011 at 9:33 am
tbailey 19088 (8/11/2011)
Steve,You ask if a copy of the database encryption key is needed to restore a backup and then you answer that a copy of the server certificate used to create the key is needed to restore the database. This incorrect answer is based on conflating the DEK with the certificate used to encrypt the DEK.
Check out the CREATE DATABASE ENCRYPTION KEY syntax at http://technet.microsoft.com/en-us/library/bb677241.aspx.
According to TechNet:
"The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key."
And making the distinction even clearer:
"The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database."
So I think you're either confused, or you're applying a deeper technical point definition here than is needed.
The DEK CAN be backed up. There is a BACKUP MASTER KEY (http://msdn.microsoft.com/en-us/library/ms174387.aspx), which by definition exports a copy of the key. This is an asymmetric key, which technically might not be a certificate, but for practical purposes is the same. It is portable to other systems.
A restore of the DEK will then be encrypted and protected by the Service master key on the new system.
If you restore a backup from one system to another that is protected by TDE, you need a copy of that database master key from the original system.
August 11, 2011 at 10:18 am
mtassin (8/11/2011)
I figured it had to require the key, because encrypted anything just about always does.So I was like no duh.... then I was like "If it's such a no-duh question, then there has to be a trick answer"
Then I started running psychological analysis loops until I eventually recursed myself into oblivion.
Finally, I went with my original answer, and got it right.
Thanks a lot for making me second and third guess myself.
:laugh: I did the same thing. The "normal backup" threw me for a loop, as I couldn't figure out if they meant a pre-TDE-enabled backup. Definitely not an unambiguous question. I just went with my gut instinct on this one (which, incidentally, is usually wrong -- I'm just sort of accustomed to being wrong).
August 11, 2011 at 11:05 am
I'm in the process of encrypting our databases and I have been reading up on it a lot the past two weeks. I also didn't consider DEK to be certificate. From all my research, you need to back up the master key and certificate because these two are crucial for db restore to another server. I got the question wrong too 🙁
August 12, 2011 at 8:21 am
I have thought the key word is different server and it looked obvious that it needs a copy of the key. But that was more like a feeling. After reading msdn:
The certificate that was used to protect the DEK when the file was written must be on the server for these files to be restored or reloaded. Thus, you must maintain backups for all certificates used, not just the most current certificate.
I am more confident in the answer.
Regards,
Iulian
August 12, 2011 at 8:25 am
The certificate is separate from the DEK even though they work together. You create the certificate first using master database and needs to be backed up. Then for each db that you are going to encrypt, you create the DEK.
August 21, 2011 at 3:24 pm
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply