question

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 asked ErlandSommarskog commented

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

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.


sql-server-generalsql-server-transact-sqlazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CharlieLor-4693 Thank you for posting your question. Is it for an Azure SQL database or Managed Instance?

Regards,
Oury

0 Votes 0 ·

Manage instance.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @CharlieLor-4693,

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://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would recommend that if take this route, that you work with a copy of the production database. Setting the database to simple recovery will break the log chain, and this can bite you, if it later turns out that you had corruption that had not yet been detected.

In such a situation, you may need to restore a backup which is a couple of weeks old and apply transaction logs. Which you can't if you have broken the log chain.

Or, as I suggested, send the vendor an encrypted backup and the encryption key - but through two different channels.

0 Votes 0 ·