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.