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\MyEncryptedDB.bak' WITH
MOVE N'MyEncryptedDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyEncryptedDB_Temp.mdf',
MOVE N'MyEncryptedDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyEncryptedDB_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 MyEncryptedDB_Temp SET ENCRYPTION OFF;
GO

++ Remove Original Database Encryption Key

USE MyEncryptedDB_Temp;
GO
DROP DATABASE ENCRYPTION KEY;

++ Create a new certificate to encrypt temporary database

USE master;
GO
CREATE CERTIFICATE MyTempTDECert
WITH SUBJECT='Certificate for encrypting temporary DB';
GO

++ Create new database encryption key

USE MyEncryptedDB_Temp;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyTempTDECert;

++ Backup the new temporary certificate

USE master;
GO
BACKUP CERTIFICATE MyTempTDECert
TO FILE = 'C:\temp\MyTempTDECert.cer'
WITH PRIVATE KEY (file='C:\temp\MyTempTDECert.pvk',
ENCRYPTION BY PASSWORD='Provide Strong Password for Backup Here');

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

ALTER DATABASE MyEncryptedDB_Temp SET ENCRYPTION ON;
GO

++ Backup the new temporary database

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

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

C:\temp\MyEncryptedDB_Temp.bak
C:\temp\MyTempTDECert.cer
C:\temp\MyTempTDECert.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'MyEncryptedDB_Temp';
GO
USE master;
GO
DROP DATABASE MyEncryptedDB_Temp;
DROP CERTIFICATE MyTempTDECert;

 

Hope this helps !! Happy Sharing !!


Comments (0)

Skip to main content