TDE DR

Question

TDE DR

Steve Jones - SSC Editor

SSC Guru

Points: 736188
More actions
July 11, 2013 at 8:12 pm

#74474

Comments posted to this topic are about the item TDE DR
July 12, 2013 at 7:36 am

This was removed by the editor as SPAM
July 12, 2013 at 7:50 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Being unaccustomed of Encryption features, made me read about the TDE and what it takes to move the TDE protected database before marking the correct answer.

Thanks Steve, it is always feels good when we learn new things 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Nice question Steve, definately learned something.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Nice question!

What if you already have a master key that is used by a certificate aimed for another database (dbA) on the instance you're moving the dbB?

Just for clarification.

I think you should drop the dbA certificate (backup before) using the old master service key, then drop the master key and recreate with another password (same as for dbB certificate), and then create the new certificate from the cert and key files you moved on the new instance, using the new master key?

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

paul s-306273 SSChampion Points: 10730 More actions · Answer 4

Learnt something new - not the answer that I was expecting.

logitestus SSCrazy Points: 2928 More actions · Answer 5

Foiled again!

Interestingly, what is on MSDN and what is in BOL is not the same! Though, I suspect what is on MSDN to be more accurate...

Great question, I have definitely spent 30 minutes delving into something I have never touched on in SQL Server.

Thanks!

OCTom SSChampion Points: 11785 More actions · Answer 6

I must be missing something. I'm sure someone can put me straight. This link says that you need to restore both the DEK and the certificate http://msdn.microsoft.com/en-us/library/bb934049.aspx. I chose the first answer because of this.

When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.

Thanks,

Tom

Error Handler Ten Centuries Points: 1086 More actions · Answer 7

Nice Question. Actaully I did TDE enabled database on different SQL Server instance. I just took the backup of CERTIFICATE and Privatekey, and restore them on new SQL Server instance.

Best,

Naseer

Best,
Naseer Ahmad
SQL Server DBA

Dana Medley SSCertifiable Points: 6764 More actions · Answer 8

logitestus (7/12/2013)
Foiled again!
Great question, I have definitely spent 30 minutes delving into something I have never touched on in SQL Server.

+1 Agreed. I have learned something new today. Thanks Steve!

Everything is awesome!

sestell1 SSChampion Points: 10230 More actions · Answer 9

OCTom (7/12/2013)
I must be missing something. I'm sure someone can put me straight. This link says that you need to restore both the DEK and the certificate http://msdn.microsoft.com/en-us/library/bb934049.aspx. I chose the first answer because of this.
When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.
Thanks,
Tom

I selected the same answer for the same reason.

Is the private key mentioned referring to the DEK, and is it backed up automatically with the certificate?

tbailey 19088 SSCrazy Points: 2317 More actions · Answer 10

Many of the encryption concepts in SQL Server are pretty opaque to me. I thought the certificate was useless without its private key file. But can you create a backup of the certificate tha includes the private key file? the documentation pointed to seems to suggest this.

Ken SSCoach Points: 17191 More actions · Answer 11

MSDN confused me on this one. Oh well, learned something new. Thanks for the question Steve!

ahperez SSC Eights! Points: 962 More actions · Answer 12

OCTom and sestell1

+1 I chose the same answer, and at least we are erring on the side of caution!

Steve, great question which cleared up my misunderstanding of the need for other items besides the certificate to be available for a restore operation.