How to create a decrypted copy of an encrypted database after obfuscation and export to another server

Question

The backup from production was created with forced encryption and based on a certificate which included a .cer and .p7k export with a password.

The password and .cer/.p7k has been passed to a test environment and the certificate is installed with a second password. An obfuscated copy of the production copy was restored using the certificate and password copied and stored on the test server.

The test environment does not use encryption other than the step above used to decrypt the obfuscated copy.

When the above copy is used to create a new backup on the test server, that backup retains the encrypted status when exported to the dev environment.

How can the encrypted backup be created without decryption on the test server such that the unencrypted but obfuscated backup can be sent to dev without providing the certificate/password used to decrypt the same in the test environment?

If needed, the code to create both the production certificate and it's steps to export to a secondary test environment can be provided.

ENCRYPTION STEPS IN PRODUCTION

• If not already performed, backup the service master key with a password and save the password in a safe location
• Create the Certificate (needs a name and subject)
• Backup the certificate with a private key (and password)
• Create Database encryption with AES256 Key that is encrypted by the certificate created in the last step
• If one database or more require encryption alter the database encryption (set encryption on)
• Obfuscate then backup the encrypted database to a ".bak" file [to be exported to a target test server]
• Backup the above certificate to a .cer and .p7k with the password
• SWITCH TO THE TEST SERVER
• Using the newly created .cer and .p7k, on the target server, create a master key with a password
• Using that password, open the master key using "decryption by password"
• Using restore from, create a certificate on the target server using the .cer and .p7k imported from above

These steps will allow the encrypted backup to be restored to the SQL Server.

NEEDED:
I'm running into difficulty creating an unencrypted copy of this obfuscated database, backed up in production and exported to the target server using the steps above - and creating a backup of same that can be exported to a DEV environment.

Any assistance is appreciated.

Answer 1

/* PROBLEM - You need to decrypt the database now that the one on the target server has been obfuscated but is still encrypted */

/* is the database protected by the certificate? */
Select db_name(ek.database_id)dbName,* from sys.dm_database_encryption_keys ek
join sys.certificates cr on ek.encryptor_thumbprint=cr.thumbprint

/* if the db is certificate protected and you have the cert password, you can turn it off */
USE Master
GO
ALTER DATABASE CLEANDB SET ENCRYPTION OFF

USE CLEANDB
GO
DROP DATABASE ENCRYPTION KEY
GO

USE master
GO
DROP CERTIFICATE RESTORE_TDE
GO

/* Should be gone now */
Select db_name(ek.database_id)dbName,* from sys.dm_database_encryption_keys ek
join sys.certificates cr on ek.encryptor_thumbprint=cr.thumbprint

/* Back it up now without the certificate */
BACKUP DATABASE [CLEANDB] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\CLEANDB\CLEANDB_Unencrypted_JUN_24_21.bak' WITH NOFORMAT, NOINIT,  NAME = N'CLEANDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

An issue that presents itself here: these steps are necessary for each iteration of the clean database brought in. Steps are -

1. Enroll the source certificate on the target server
2. Restore the encrypted (obfuscated - clean) database to target
3. Remove the encryption using the steps above
4. Back up the database to be exported to another target like development or another test database