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 Friday, July 12, 2013 11:22 AM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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


DEK is in the backup of the TDE database.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473159
Posted Friday, July 12, 2013 11:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:53 AM
Points: 2,667, Visits: 4,091
Steve Jones - SSC Editor (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


DEK is in the backup of the TDE database.


It's Friday. My mind must have shut down. What you are saying here, Steve, makes no sense to me. I will research this and see if I can clear it up. I have not worked with encryption. It will be good to look into it, though on another day.

Thanks,
Tom
Post #1473181
Posted Friday, July 12, 2013 12:06 PM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
It's worded poorly in BOL.

Essentially on the source you:
- create master key (protected by SMK) in master.
- create cert, protected by DMK (master key).
- you backup the cert, decrypting it using the password from the previous step, and assign a (new hopefully) password to the backup. You do this first. Backup certs/keys before you do anything else!
- you create a DEK in the db you are encrypting, protected by the cert.
- you enable TDE
- you backup the TDE database. The DEK is inside this backup as part of the meta data, but it's encrypted and protected by the cert, which is NOT in the backup.

On a new instance, say in a DR situation or movement to a new instance.
- you create a master key if there isn't one. If there is, you just need a password protecting this.
- you create (from file, as restore) the certificate from the backup above. You need the password protecting the files.
- Now you restore the TDE database. The cert exists, so the instance uses this to decrypt the DEK in the database, subsequently decrypting the data when requested by the new instance.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473197
Posted Friday, July 12, 2013 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
Steve Jones - SSC Editor (7/12/2013)
It's worded poorly in BOL.

Essentially on the source you:
- create master key (protected by SMK) in master.
- create cert, protected by DMK (master key).
- you backup the cert, decrypting it using the password from the previous step, and assign a (new hopefully) password to the backup. You do this first. Backup certs/keys before you do anything else!
- you create a DEK in the db you are encrypting, protected by the cert.
- you enable TDE
- you backup the TDE database. The DEK is inside this backup as part of the meta data, but it's encrypted and protected by the cert, which is NOT in the backup.

On a new instance, say in a DR situation or movement to a new instance.
- you create a master key if there isn't one. If there is, you just need a password protecting this.
- you create (from file, as restore) the certificate from the backup above. You need the password protecting the files.
- Now you restore the TDE database. The cert exists, so the instance uses this to decrypt the DEK in the database, subsequently decrypting the data when requested by the new instance.

So what you need is a backup of the certificate that protects the DEK plus the password protecting that backup. Of course if you have the certificate without needing that password you have done something horribly insecure somewhere, but the QotD doesn't ask what you need in the absence of horrible insecurity so maybe it's fair enough not to mention a need for that password? ?? ??? Or am I still misunderstanding?


Tom
Post #1473211
Posted Friday, July 12, 2013 12:47 PM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
I'm not sure what you mean about insecure if you have the cert without the password. You could have a blank password, but in that case you'd technically have the password.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473217
Posted Friday, July 12, 2013 5:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
Steve Jones - SSC Editor (7/12/2013)
I'm not sure what you mean about insecure if you have the cert without the password. You could have a blank password, but in that case you'd technically have the password.

OK, that sounds as if you are saying that if you have the cert then of course you have the password, because there's no way you can have the cert without having the password. That of course is true, but whether it's a justification for saying you just need the cert, no need to mention the password, is maybe debatable. As for insecure - if you have a blank password I think that's insecure; it's one of the first things I would try, even before I started trying to find out wife's maiden name, first school, favourite porn star, and so on; I have come across blank passwords far too often to think that it's pointless trying them first, and that makes them utterly insecure.


Tom
Post #1473263
Posted Sunday, July 14, 2013 2:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:53 PM
Points: 1,431, Visits: 687
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:

backup certificate ServerCertificate to file = 'SomePath' 
with private key (file = 'SomePath', encryption by password = 'SomePassword');

So I am waiting for a reply from the experts.

Warm Regards,
Hany Helmy
Post #1473392
Posted Monday, July 15, 2013 3:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:04 AM
Points: 924, Visits: 167
New thing to me...Thanks Steve....
Post #1473545
Posted Monday, July 15, 2013 9:38 AM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
hany.helmy (7/14/2013)
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:

backup certificate ServerCertificate to file = 'SomePath' 
with private key (file = 'SomePath', encryption by password = 'SomePassword');

So I am waiting for a reply from the experts.

Warm Regards,
Hany Helmy


The DEK is inside the database backup. It is not backed up separately, and actually cannot be.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473731
Posted Monday, July 15, 2013 1:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:53 PM
Points: 1,431, Visits: 687
Steve Jones - SSC Editor (7/15/2013)
hany.helmy (7/14/2013)
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:

backup certificate ServerCertificate to file = 'SomePath' 
with private key (file = 'SomePath', encryption by password = 'SomePassword');

So I am waiting for a reply from the experts.

Warm Regards,
Hany Helmy


The DEK is inside the database backup. It is not backed up separately, and actually cannot be.


Ok, Got it thanx Steve.
Post #1473868
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse