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
Improve this question
9
  • 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.
    – Hannah Vernon ♦
    Aug 26, 2014 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, 2014 at 17:07
  • Kenneth: Everything is working correctly. This inquiry is in the name of a possible DR.
    – Dylon
    Aug 26, 2014 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, 2014 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.
    – Hannah Vernon ♦
    Aug 26, 2014 at 17:42

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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