Issue with TDE using identity when doing backup encryption

xia0lin 0 Reputation points
2024-04-18T06:15:29.37+00:00

Hi,

I have TDE enabled on two different servers and I've been using a script to transfer encrypted data without decryption for years. The script I used to create the TDE is:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xx';

CREATE CERTIFICATE PasswordCert WITH SUBJECT = 'XX Subject';

CREATE SYMMETRIC KEY PasswordKey WITH

KEY_SOURCE = 'xx source',ALGORITHM = AES_256,IDENTITY_VALUE = 'xx value'

ENCRYPTION BY CERTIFICATE PasswordCert

Recently, I applied backup encryption on these servers using different certificate and password, with the following script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YYY'

CREATE CERTIFICATE YYCert WITH SUBJECT='YYY Certificate'

The backup process succeeded, but the data transfer is not working properly because I can't decrypt the encrypted data on the destination server using the following script:

OPEN SYMMETRIC KEY PasswordKey

DECRYPTION BY CERTIFICATE PasswordCert;

select CONVERT(varchar, DecryptByKey(columnA)) from tableA

Kindly Advice and thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,738 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CosmogHong-MSFT 23,166 Reputation points Microsoft Vendor
    2024-04-18T06:42:43.84+00:00

    Hi @xia0lin

    Have you restored the TDE encrypted database?

    Referring from this doc: TDE and backups.

    Please also check if the certificate was created in the master database. When you restore from these backups, the certificate protecting the DEK must be available. This means that in addition to backing up the database, you must maintain backups of the server certificates to prevent data loss.

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".