Restore AWS RDS SQL Server TDE-encrypted .bak to local SQL Server (on-prem)

Please note that the error “Cannot find server certificate with thumbprint '0x...’” occurs when you try to restore a database with Transparent Data Encryption (TDE) from EC2 or on-premises to RDS for SQL Server or vice versa.

In simple words, This error indicates an attempt to restore a backup of a database that is encrypted using TDE to a SQL instance other than its original server. Instead of saying that “Destination do not have the certificate with name identifier from the source instance", the error is referred to the certificate using the thumbprint of the certificate.

The TDE certificate of the original server must be imported to the destination server.

That being said, Yes, it's possible to restore a TDE-encrypted RDS SQL Server backup to an on-premises SQL Server instance, but you'll need to transfer the TDE certificate from your RDS instance to your local SQL Server first.

Here's the process to restore your TDE-encrypted backup:

1. Back up the TDE certificate from your RDS SQL Server instance:

• Connect to your RDS instance using SQL Server Management Studio
• Identify the TDE certificate name for your database
• Back up the certificate and its private key

2. Use AWS KMS to generate a data key for securing the certificate backup:

• The private key will be encrypted using this data key
• The encrypted data key (cipher text) will be stored in the S3 metadata

3. Upload the certificate backup files to an S3 bucket

4. On your local SQL Server:

• Copy the TDE certificate backup file and private key file from S3 to your local instance
• Decrypt the data key using AWS KMS to retrieve the plain text password
• Restore the certificate using the SQL command:

CREATE CERTIFICATE myOnPremTDEcertificate 
FROM FILE='path\to\tde-cert-backup.cer'
WITH PRIVATE KEY (FILE = 'path\to\tde-cert-key.pvk',
DECRYPTION BY PASSWORD = 'plain_text_output');

5. After the certificate is restored, you can restore the database backup using the standard RESTORE DATABASE command, specifying the backup file location and using WITH MOVE to place data and log files in appropriate locations.

This process requires careful handling of encryption keys and certificates, so ensure you follow security best practices. It's recommended to test this procedure in a non-production environment before attempting it with production data.

With that said, If you encounter any issues while following the above method you can always open a support case with AWS Support Engineering Team. Certain non-public information might be required such as your RDS instance and IAM role details. Hence you can open a support case with AWS using the following support link.

[+] : AWS Support team - https://console.aws.amazon.com/support/

Sources
Backing up and restoring TDE certificates for on-premises databases - Amazon Relational Database Service
Restore TDE Encrypted Multi-AZ RDS MS SQL server to self hosted MS SQL server on EC2 | AWS re:Post