TDE DR

  • Comments posted to this topic are about the item TDE DR

  • 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

  • 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

  • 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

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

  • 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!

  • 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

  • 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

  • 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!

  • This was removed by the editor as SPAM

  • 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?

  • This was removed by the editor as SPAM

  • 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.

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

  • 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.

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

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