Sharing TDE Encrypted Backup outside the organisation.

In order to share the TDE Encrypted Database backup with somebody outside the organisation, the below steps can be followed.

++ Create new temporary database in order to prepare a make-shift copy of the intended database.

RESTORE DATABASE MyEncryptedDB_Temp FROM DISK = N'C:\Temp\EncryptedDatabase.bak' WITH
MOVE EncryptedDatabase' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\EncryptedDatabase_Temp.mdf',
MOVE N'EncryptedDatabase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\EncryptedDatabase_Temp_log.ldf';
GO

++ Disable TDE on the new temporary database in order to reset the certificate and the DEK and then re-enable it with the new ones.

ALTER DATABASE EncryptedDatabase_Temp SET ENCRYPTION OFF;
GO

++ Remove Original Database Encryption Key

USE EncryptedDatabase_Temp;
GO
DROP DATABASE ENCRYPTION KEY;

++ Create a new certificate to encrypt temporary database

USE master;
GO
CREATE CERTIFICATE TemporaryTDECert
WITH SUBJECT='To encrypt temporary DB';
GO

++ Create new database encryption key

USE EncryptedDatabase_Temp;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TemporaryTDECert;

++ Backup the new temporary certificate

USE master;
GO
BACKUP CERTIFICATE TemporaryTDECert
TO FILE = 'C:\temp\TemporaryTDECert.cer'
WITH PRIVATE KEY (file='C:\temp\TemporaryTDECert.pvk',
ENCRYPTION BY PASSWORD='Password to backup');

++ Enable TDE on the temporary Database. This time the database will pick the newly created certificate for encryption.

ALTER DATABASE EncryptedDatabase_Temp SET ENCRYPTION ON;
GO

++ Backup the new temporary database

BACKUP DATABASE EncryptedDatabase_Temp TO  DISK = N'C:\temp\EncryptedDatabase_Temp.bak';

++ Provide outside organization with database backup, certificate backup, and private key backup files.

C:\temp\EncryptedDatabase_Temp.bak
C:\temp\TemporaryTDECert.cer
C:\temp\TemporaryTDECert.pvk

++ Now as we have achieved the required outcome of sharing the TDE encrypted database outside the organisation, we can clear the make-shift temporary database and certificate.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'EncryptedDatabase_Temp';
GO
USE master;
GO
DROP DATABASE EncryptedDatabase_Temp;
DROP CERTIFICATE TemporaryTDECert;

 

Hope this helps !! Happy Sharing !!