SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TDE DR


TDE DR

Author
Message
OCTom
OCTom
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3147 Visits: 4152
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
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 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?
Revenant
Revenant
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7427 Visits: 4865
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...
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64277 Visits: 19117
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...


Wink

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
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64277 Visits: 19117
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
My Blog: www.voiceofthedba.com
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 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]
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 319
Pour mois, nul points.

Good thing I'm a developer, not a DBA.
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4315 Visits: 2774
Nice question and definitely learned something.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33008 Visits: 18560
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

jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 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
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