1

Back story: My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, the password it was encrypted with is lost too.

We also have the certificate backed up, but those passwords are lost as well.

The original backup files (master key, cert) are still in existence and those encryption passwords are available for previous servers these DBs lived on.

Question:

  1. We want to have the actual passwords on file for the certs/keys used in our environment. What is the best solution? - This is a production environment
  • 3
    Seriously work on migrating all the structures in those encrypted databases into new databases where you know the key passwords. Do this as soon as possible since if you have a failure of the server necessitating a re-install, etc, good luck getting access to the data. – Max Vernon Aug 26 '14 at 16:56
  • Are you currently in a DR situation or is everything working correctly currently but you just don't have your passwords for a possible DR in the future? – Kenneth Fisher Aug 26 '14 at 17:07
  • Kenneth: Everything is working correctly. This inquiry is in the name of a possible DR. – Dylon Aug 26 '14 at 17:10
  • Max: I don't think migrating data from these servers is a good option. It just doesn't seem necessary. In case of a DR, I think we would be able to create a new cert on a new server (from the old server's cert/key backup - This PW is available), and create a new master key to encrypt the new certificate by(which would be created from the original server's backup files). Please correct me if I'm wrong. – Dylon Aug 26 '14 at 17:15
  • 1
    @Dyl.DBA - That may work. I would suggest very strongly before you need your DR to work, that you test that hypothesis. Restore one of the databases onto a test or dev platform and see if you can do what you propose. You don't want to find out it didn't work at the worst possible moment. – Max Vernon Aug 26 '14 at 17:42
2

My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, the password it was encrypted with is lost too.

We also have the certificate backed up, but those passwords are lost as well.

So you know the backup cert/DMK files are now useless. I would IMMEDIATELY take new backups and save the passwords. This way you can restore older backup files should anything happen. I would also backup the SMK as that's the only means you have to decrypting the DMK/Cert at this moment.

The original backup files (master key, cert) are still in existence and those encryption passwords are available for previous servers these DBs lived on.

I'm assuming the keys haven't been rotated per your comment. If this is the case you are most likely able to restore these to a test server and restore your TDE enabled databases to said test server without issue. If this is the case, you could re-use these keys assuming nothing else is relying on the DMK/Cert in your current instance.

Since TDE works through the SMK (and not by passwords) you're currently "at risk". Sure, it's running but who knows when or if something will happen. I stated before, take new backups.

The most straight forward approach would be to remove TDE from the databases on that instance, remove the cert, and remove the DMK (After backing them up first). Then proceed to create the DMK/Cert/TDE enabled. It's going to be disk and cpu intensive for a bit (depending on the size of database and hardware/disk/etc). Backup the new DMK/Cert with a guarded password.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.