July 16, 2013 at 7:58 am
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
July 16, 2013 at 8:51 am
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?
July 16, 2013 at 8:54 am
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...
July 16, 2013 at 8:58 am
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.
July 16, 2013 at 9:03 am
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.
July 16, 2013 at 9:49 am
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
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]
July 17, 2013 at 6:29 am
Pour mois, nul points.
Good thing I'm a developer, not a DBA.
July 24, 2013 at 1:36 am
Nice question and definitely learned something.
July 25, 2013 at 10:07 am
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[/url]
Learn Extended Events
August 29, 2013 at 8:27 am
learned something today
interesting to note than correct answer ratio goes definitively lower when one can NOT copy / paste the question in SSMS
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply