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.