I have been doing some testing around TDE, in anticipation of enabling in my environments

I have TDE enabled on a database which means I cannot restore or attach this database to another server without a copy of the certificate that encrypts it

  • I backup the TDE database to ServerA nightly (general backup server)
  • I backed up the certificate to ServerB (DR server) as soon as it was created, prior to enabling TDE
  • I also backup other databases on this instance including all the system databases, to ServerA (all database backups for this instance are in the same folder)

I have done some testing and have found that if I have both the master database backup and the TDE database backup, I can restore the master database to another server (assuming it is the same version) and then restore the TDE database.

I have also found that if I have both the master database mdf / ldf and the TDE database mdf/ldf(s) I can attach the master database files to another server (again, assuming same version) and then attach the TDE database

Given that the master and TDE databases are backed up to the same folder on the backup server and that the attached system databases and user databases are in the same folder on the SQL server, this appears to be mean that I am storing the certificate for the TDE database along with the TDE database itself and therefore if an attacker can get both, they would be able to attach the TDE database to another server.

Since both files are in the same folder(s) with the same permissions, it is likely that if an attacker was able to get the backup / attached files for the TDE database, they would also be able to get the backup / files for the master database

What would be a good practice here to remove this vulnerability?

I would think I could solve / mitigate the backup issue by backing up the master database to a different server (perhaps ServerB) However, what is a good practice for storage of the attached master / TDE database files given that they need to be attached to the same instance?

  • There's a post on this by Simon McAuliffe:- medium.com/@s.mcauliffe_17464/… For me, TDE's dubious protection isn't worth the hassle. Last time I was 'forced' to consider TDE, I investigated using an HSM to mitigate some of the issues, but the HSM licencing and 'mandatory' consultancy was ruinously expensive. In the end we ticked the compliance boxes by hosting all the database files & backups on an encrypted SAN, with an agent pulling the backups onto an encrypted tape device. Jan 11, 2023 at 18:03


Your Answer

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