Best practice around non-production databases refreshes and TDE

Say we have a production system with several databases encrypted using TDE and a self-generated certificate.

We regularly need to take this database and refresh our non-production systems. This process involves backing up the source database, restoring it to pre-production, obfuscating personal data and a number of other items.

The key fact here is that the pre-production system has a copy of the encryption certificate from production

I am looking at ways to make this process more secure - I'm not happy with the certificate sat on the pre-production environment. Is there another way that will:

• Never have an unencrypted backup saved anywhere

• Not allow propagation of personal data from production into non-production systems?

In a perfect world, if you’re storing personally identifiable data in production, you never restore the database to a development environment.

You can’t rely on obfuscation alone. For example, sooner or later, someone’s going to make a backup copy of a table in production. They’re going to select the dbo.Customers table into a new table for safekeeping before they make a change to it, and they’re going to forget to drop it. Your obfuscation code won’t be expecting new random tables with personally identifiable data, and so after the obfuscation process, the data will still be there.

Instead, when you need development data, generate it from scratch.

(Remember: you asked for the best practice, not a compromise. If your response is, “but we want production data in development,” then you’re already missing the best practice.)

StackExchange Database Administrators Q#218962, answer score: 7