Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

TDE DR Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 8:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:32 AM
Points: 33,186, Visits: 15,322
Comments posted to this topic are about the item TDE DR






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1472842
Posted Thursday, July 11, 2013 11:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1472856
Posted Friday, July 12, 2013 12:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 13,605, Visits: 10,486
Nice question Steve, definately learned something.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1472873
Posted Friday, July 12, 2013 4:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 2,931, Visits: 2,951
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,
SQL Server developer at Seavus
www.seavus.com
Post #1472933
Posted Friday, July 12, 2013 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Learnt something new - not the answer that I was expecting.
Post #1472972
Posted Friday, July 12, 2013 6:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 254, Visits: 317
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!
Post #1472982
Posted Friday, July 12, 2013 6:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 2,553, Visits: 3,799
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
Post #1472983
Posted Friday, July 12, 2013 6:51 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 657, Visits: 290
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
Post #1473001
Posted Friday, July 12, 2013 7:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 1,856, Visits: 1,394
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!
Post #1473007
Posted Friday, July 12, 2013 7:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,914, Visits: 5,092
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


No, this section says you need to back up the certificate and it's private key. The DEK is stored within the db and forms part of it's backup.

Good question, Steve, thanks


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1473020
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse