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 Tuesday, July 16, 2013 7:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 2,667, Visits: 4,091
Thank you Steve for the detailed explanation. And thank you to everyone else who participated with questions and answers. I usually get more out of the discussions than I do the original question.

I now have a tenuous understanding. I will test how this all works in my sandbox.

Tom
Post #1474140
Posted Tuesday, July 16, 2013 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
From all the discussion it still seems like aside from having the backup of the certificate file that was saved with the private key by password "abc123"
that when the TDE database is restored to a different server the certificate file is required and knowledge of what the password was.

So if the DBA that created the TDE database and saved the certificate is no longer at the company or has a bad memory ... does that mean the password is lost and the TDE db cannot be restored? if the "original" password is not known .. how do you get around that? or is it not needed?


Post #1474164
Posted Tuesday, July 16, 2013 8:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
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.

I wish I could read this post before I tried to reply. I spent over 40 minutes checking BoL...
Post #1474168
Posted Tuesday, July 16, 2013 8:58 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 1:28 PM
Points: 31,368, Visits: 15,836
Revenant (7/16/2013)
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.

I wish I could read this post before I tried to reply. I spent over 40 minutes checking BoL...


;)

Not great documentation in BOL. There's a good white paper and a couple good articles on places like Simple Talk on this.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474172
Posted Tuesday, July 16, 2013 9:03 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 1:28 PM
Points: 31,368, Visits: 15,836
Ellen-477471 (7/16/2013)
From all the discussion it still seems like aside from having the backup of the certificate file that was saved with the private key by password "abc123"
that when the TDE database is restored to a different server the certificate file is required and knowledge of what the password was.

So if the DBA that created the TDE database and saved the certificate is no longer at the company or has a bad memory ... does that mean the password is lost and the TDE db cannot be restored? if the "original" password is not known .. how do you get around that? or is it not needed?


Yes, you do need the certificate backup (and the pwd protecting it) on the new server.

A couple things. First, this works as a hierarchy of protection. One key "protects" another, which essentially means it is used to encrypt and decrypt the lower key. Here the DEK is the lower key. It is protected (encrypted), but the certificate. You need access to this certificate to decrypt the DEK on restore, which allows you to then access data.

Second, DBA leaves. You can be in trouble here. However as long as you have a valid account that can access the database on the first server, you can get the data. That's the "transparent" part. What you should do is first check if you can access the master key on the first server. If so (potentially, and likely, this is protected by the SMK), then make a backup of the certificate yourself. You can make multiple backups, and the password used in the backup is made up at the time you back up the cert. It's not something that's stored in the cert.

If you encounter TDE in your environment, make a backup of the cert ASAP. If something happens, then you can access your backups. If you don't, might want to update your resume since it will be partially your fault if data is lost after you start working with the system.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474174
Posted Tuesday, July 16, 2013 9:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
Steve:
Thank you for your reply.

Nice reminder that if a person takes on a new position one of the first things to do is
1) identify which databases are protected by TDE
2) verify there are backups of the certificates in a known & reachable location
3) secure the password[s]

in lieu of 2 & 3 existing, create the certificate backups personally with my own password
[and if the master key is not accessible to allow a certificate backup to be created .....
better report it to management ahead of time that the data will be lost if disaster occurs]
Post #1474199
Posted Wednesday, July 17, 2013 6:29 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: Tuesday, April 15, 2014 8:03 AM
Points: 825, Visits: 319
Pour mois, nul points.

Good thing I'm a developer, not a DBA.
Post #1474544
Posted Wednesday, July 24, 2013 1: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: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
Nice question and definitely learned something.
Post #1476903
Posted Thursday, July 25, 2013 10:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
Good Question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1477605
Posted Thursday, August 29, 2013 8:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:24 AM
Points: 1,144, Visits: 299
learned something today

interesting to note than correct answer ratio goes definitively lower when one can NOT copy / paste the question in SSMS
Post #1489749
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse