TDE Encrypted database fails to load on other server

Question

Dear colleagues,

I am still struggling with TDE. I used the script below to create a TDE encrypted database.
I am using SQL Server 2019 on boh servers, and yes Erland, this time the latest available CU.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22000: )).

USE master;
GO
CREATE DATABASE TDE_Test;
GO

USE TDE_Test;
GO
CREATE TABLE Test (vc VARCHAR(10));
GO
INSERT INTO Test VALUES ('Testing 1'), ('Testing 2'), ('Testing 3');
GO

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<very secret P@5Sw0rd>';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<very secret P@5Sw0rd>';
GO
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
GO

USE TDE_Test;
GO
CHECKPOINT;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE TDE_Test SET ENCRYPTION ON;
GO

USE master;
GO

/* Wait until the encryption_state = 3 and the percent_complete = 100 */
SELECT db.name AS db, db.is_encrypted, dek.encryption_state, dek.percent_complete
FROM sys.databases db
JOIN sys.dm_database_encryption_keys dek ON dek.database_id = db.database_id
WHERE db.name = 'TDE_Test';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<very secret P@5Sw0rd>';
GO
BACKUP DATABASE TDE_Test
  TO DISK = N'C:\SomePath\TDE_Test.bak' WITH NOFORMAT, INIT, NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
BACKUP MASTER KEY TO FILE = 'C:\SomePath\TDE_Master.key'
  ENCRYPTION BY PASSWORD = '<very secret P@5Sw0rd>';
GO
BACKUP CERTIFICATE TDE_Certificate
  TO FILE = 'C:\SomePath\TDE.cer';
GO

When I try to load this database on another server with this script, I get the error
Msg 15507, Level 16, State 30, Line 14
A key required by this operation appears to be corrupted.

USE master;
GO
RESTORE MASTER KEY
  FROM FILE = 'C:\SomePath\TDE_Master.key'
  DECRYPTION BY PASSWORD = '<very secret P@5Sw0rd>'
  ENCRYPTION BY PASSWORD = '<very secret P@5Sw0rd>'
  FORCE;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<very secret P@5Sw0rd>';
GO
CREATE CERTIFICATE TDE_Certificate
  FROM FILE = 'C:\SomePath\TDE.cer';
GO
RESTORE DATABASE TDE_Test FROM DISK = N'C:\SomePath\TDE_Test.bak' WITH FILE = 1, NOUNLOAD, STATS = 5;
GO

Does anybody know what I am doing wrong?

By the way, I can make backups and do restores on the original server.
Also, I can detach and attach on the original server.
Attaching on the new server also gives me the error above.

Extra Info:
The path "C:\SomePath" is exactly the same on both servers. I read somewhere this is necessary.
When I give access to the "C:\SomePath" directory to "NT SERVICE\MSSQL$<Instance name>" and "NT SERVICE\SQLAgent$<Instance name>" on the target server, I get errors on the key file, the certificate file and the backup file.
My guess is that this is at the root of my problem.

Answer 1

Try to back up the certificate with the private key on the source server:

-- Backup the Certificate and Private Key
BACKUP CERTIFICATE TDE_Certificate
TO FILE = 'C:\SomePath\TDE.cer'
WITH PRIVATE KEY (FILE = 'C:\SomePath\TDE_CertKey.pvk', ENCRYPTION BY PASSWORD = '<very secret P@5Sw0rd>');
GO

And then create the certificate from the backup file and private key on the destination server;

-- Create the Certificate from the Backup File and Private Key
CREATE CERTIFICATE TDE_Certificate
FROM FILE = 'C:\SomePath\TDE.cer'
WITH PRIVATE KEY (FILE = 'C:\SomePath\TDE_CertKey.pvk', DECRYPTION BY PASSWORD = '<very secret P@5Sw0rd>' );
GO