What is the process to unencrypt TDE database for third-party export?

Question

If a database is TDE encrypted, what is the process to successfully backup this database as a .bak file and export to third-party vendor? Here's my process so far.

1. Unencrypt the database by using ALTER DATABASE [databaseName] SET ENCRYPTION OFF;
2. Backup the database
3. Once database backup in step 2 above is completed, re-encrypt the database by ALTER DATABASE [databaseName] SET ENCRYPTION ON;

However, this steps did not work. When the vendor try to reimport the database, the error keep saying, "Cannot find server certificate with thumbprint" and then it shows the thumbprint. Am I missing something here? I thought once it's decrypted, it's no longer needed a cert to restore.

Answer 1

Did you wait for the decryption operation to complete?

(And in the other thread you had, I actually suggested that you should try restoring the backup on a different server in your environment, to verify that the backup is OK.)

Then again, assuming that you keep the database encrypted for a reason, the above does seem like a very good idea anyway. I think you should backup the database as-is, and then pass the vendor the encryption key through a second channel.

Answer 2

Hi @CharlieLor ,

This is because you will not be able to restore database until you get the Certificate, the Private key and the password.
You need create a master key:

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  

Create or obtain a certificate protected by the master key:

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  

And then back up the certificate and its associated private key:

BACKUP CERTIFICATE MyServerCert TO FILE = 'D:\MyServerCert.cer'  
WITH PRIVATE KEY(FILE = 'D:\MyServerCert.prvk',  
ENCRYPTION BY PASSWORD = 'StrongPassword'  
)  

Protect your database by using the certificate.
After completing these operations above, you will have no problem with these operations.

For more information you can refer to the official documentation:
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

Answer 3

I finally found this article that goes through the steps to do what I wanted to do. It seems to resolve the issue I'm having. I will verify with another database to see if it is still working.

These are the steps in case this article is removed in the future.

1. Alter the database to have the ENCRYPTION option set to the value of OFF. This decrypts the database and can take some time if the database is large. If there are no other database using TDE then an unencrypted TempDB will be created next time the instance starts.

USE MASTER
GO
ALTER DATABASE {Database Name}
SET ENCRYPTION OFF
GO

2. Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of "1" returned in the encryption_status column indicates that the decryption is complete.
3. Drop the database encryption key for the database.

USE {Database Name}
GO
DROP DATABASE ENCRYPTION KEY
GO

4. Truncate the database log file. This will remove all of the data contained within the log file, including any data that is still encrypted.
5. Set the database recovery mode to simple and then shrink the log file of the database. This removes any encrypted headers that are in the database. Once this has been done, the recovery mode can be set to Full if required. This step will cause the header of the log file to be rewritten, this is important as the header may still be encrypted even after TDE is removed. Note: If you switch back from Simple to Full logging, you should take a full back up immediately to re-establish the log chain.
6. Restart the instance in which the database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.