Blitz Result: Transparent Data Encryption and Certificates
SQL Server 2008 introduced Transparent Data Encryption – a set-it-and-forget-it way to keep your databases protected on disk. If someone steals your backup tapes or your hard drives, they’ll have a tougher time getting access to the data. Just set it up and you’re done.
However, if you don’t back up your encryption certificate and password, you’ll never be able to restore those databases!
This part of our SQL Server sp_Blitz script checks sys.databases looking for databases that have been encrypted, and also checks to make sure that the certificates have been backed up recently (in the last 30 days).
You can learn much more about both TDE and the business needs behind it at the SQL Server 2008 Compliance Portal. We highly recommend the SQL Server Compliance Guide which explains these issues in clear, easy-to-understand terms.
To Fix the Problem
First, make sure you can restore the encrypted databases onto another server using this guide from Microsoft. Then, make sure you have the certificate and password backed up in a safe place – if you store them with your regular backups, then you’re still at risk of data theft.
After implementing TDE on any user database, be aware that TempDB is permanently encrypted. Even if you remove encryption, you’ll still have the encryption overhead on TempDB. This is a great reason why all testing should be done in development environments first rather than trying it in production.