Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TDE DR


TDE DR

Author
Message
sestell1
sestell1
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2209 Visits: 3418
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?
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5826 Visits: 7142
sestell1 (7/12/2013)
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?

No, the private key refers to the certificate.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
tbailey 19088
tbailey 19088
SSC Eights!
SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)SSC Eights! (983 reputation)

Group: General Forum Members
Points: 983 Visits: 231
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 Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4452 Visits: 2343
MSDN confused me on this one. Oh well, learned something new. Thanks for the question Steve!
ahperez
ahperez
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 635
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.
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1992 Visits: 6214
KWymore (7/12/2013)
MSDN confused me on this one. Oh well, learned something new. Thanks for the question Steve!


Same here! The TDE article did seem to imply you needed both backups...ah well, always good to learn something.
ssimmons 2102
ssimmons 2102
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 287
I also went to MSDN and came up with the wrong answer, oh well.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10769 Visits: 12019
I got this wrong. Looking at the reference provided (which was one of the pages that I read carefully before choosing an option) I found that the code example for restoring on a different machine included
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO


That second comment line very clearly states that the password is required as well as the certificate; the password is used twice, once for MKE and once in restoring the certificate. So it's pretty clear that either the "correct" answer is wrong or the page referenced contains a big error. I also guessed that the SQLPrivateKeyFile was an encrypted file also required, since the qualifier of the private key description is DECRYPTION BY PASSWORD and not ENCRYPTION BY PASSWORD.

The explanation provided for the answer is just a simple restatement of the answer, which makes it an utterly pointless explanation - it would have been nice to have an explanation that made some attempt to explain the answer rather than just repeat it.

Probably I'm misinterpreting something here, but I can't see what. Is anyone willing and able to explain it to me, please?

Tom

jlennartz
jlennartz
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1197
L' Eomot Inversé (7/12/2013)
I got this wrong. Looking at the reference provided (which was one of the pages that I read carefully before choosing an option) I found that the code example for restoring on a different machine included
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO


That second comment line very clearly states that the password is required as well as the certificate; the password is used twice, once for MKE and once in restoring the certificate. So it's pretty clear that either the "correct" answer is wrong or the page referenced contains a big error. I also guessed that the SQLPrivateKeyFile was an encrypted file also required, since the qualifier of the private key description is DECRYPTION BY PASSWORD and not ENCRYPTION BY PASSWORD.

The explanation provided for the answer is just a simple restatement of the answer, which makes it an utterly pointless explanation - it would have been nice to have an explanation that made some attempt to explain the answer rather than just repeat it.

Probably I'm misinterpreting something here, but I can't see what. Is anyone willing and able to explain it to me, please?


+1
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36340 Visits: 18752
IgorMi (7/12/2013)
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


the master key is part of the encryption hierarchy. It can protect more than one certificate or asym key.

If you already have a master key on the new instance, you just restore the certificate, and have it protected by the master key. No need to drop anything.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search