Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted. If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?
The easiest way to share a TDE enabled database to an outside organization is to provide the outside organization the encrypted database backup, plus the backup files associated with the certificate, that was used to encrypt the database. Even though this is an easy way to provide them all the pieces they need to restore an encrypted database backup to a new target instance, I wouldn’t recommend using this approach. The reason I’m not recommending this method is because this method provides them the real certificate and private key file that you are using on your databases. Therefore, instead of using the above method to provide a TDE encrypted backup to an outside organization let’s look at another method.
In order to provide a TDE encrypted backup to an outside organization without providing them backup files associate with the real certificate you will need to create a new backup of your TDE enabled database that is encrypted with a different certificate. To accomplish this, you can use the following steps:
Step 1: Create new temporary database
Restore your TDE enable database backup to a different database name on the same instance where the database backup was taken, or an instance that contains the certificate that was used to originally encrypt the database. You can use this code to restore the database backups to a new temporary database:
RESTORE DATABASE MyEncryptedDB_Temp FROM DISK = N'C:TempMyEncryptedDB.bak' WITH MOVE N'MyEncryptedDB' TO N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAMyEncryptedDB_Temp.mdf', MOVE N'MyEncryptedDB_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAMyEncryptedDB_Temp_log.ldf'; GO
Here I have restored a backup of my originally encrypted database named “MyEncryptedDB” to a temporary database name “MyEncryptedDB_Temp”.
Step 2: Disable TDE on the new temporary database
By disabling TDE on a TDE enable database SQL Server will unencrypt the database. You need to first unencrypt the TDE enabled database, so you can re-encrypt it with a different certificate. Use the following code to disable encryption, so behind the scenes SQL Server can decrypt your database:
ALTER DATABASE MyEncryptedDB_Temp SET ENCRYPTION OFF; GO
Step 3: Remove Original Database Encryption Key
Before you encrypt your temporary database with a new encryption key you need to remove the old database encryption key. This old key is stored in the database, so it was moved to the temporary database when it was restored. To remove that key, use the following code:
USE MyEncryptedDB_Temp; GO DROP DATABASE ENCRYPTION KEY;
Step 4: Create a new certificate to encrypt temporary database
Use the following code to create a new certificate. This new certificate will be used later to support encrypting the temporary database with a new encryption key:
USE master; GO CREATE CERTIFICATE MyTempTDECert WITH SUBJECT='Certificate for encrypting temporary DB'; GO
Step 5: Create new database encryption key
In order to enable TDE on the new temporary database a new database encryption key needs to be created and associated with the certificate created in the prior step. To create that key use the following code:
USE MyEncryptedDB_Temp; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyTempTDECert;
Step 6: Backup the new temporary certificate
In order to create the certificate backup files that you want to provide to someone outside your organization (certificate backup and private key backup) using the following code:
USE master; GO BACKUP CERTIFICATE MyTempTDECert TO FILE = 'C:tempMyTempTDECert.cer' WITH PRIVATE KEY (file='C:tempMyTempTDECert.pvk', ENCRYPTION BY PASSWORD='Provide Strong Password for Backup Here');
Note: this code generates a certificate backup file and a private key file.
Step 7: Enable TDE on the temporary Database
To encrypt the temporary database you just need to enable encryption. Use this code to enable TDE to encrypt for the temporary database:
ALTER DATABASE MyEncryptedDB_Temp SET ENCRYPTION ON; GO
Step 8: Backup the new temporary database
Use the following code to create a new database backup of the temporary database. The backup created here will be encrypted with a different certificate/key, where that certificate/key files were generated specifically for sharing with an outside organization:
BACKUP DATABASE MyEncryptedDB_Temp TO DISK = N'C:tempMyEncryptedDB_Temp.bak';
Step 10: Provide outside organization with database backup, certificate backup, and private key backup files
Provide the outside organization with the new temporary backup, along with the certificate backup and private key backup files. By providing these files your outside organization will be able to restore your TDE encrypted backup on their instance of SQL Server:
C:tempMyEncryptedDB_Temp.bak C:tempMyTempTDECert.cer C:tempMyTempTDECert.pvk
Step 11: Remove Temporary database and certificate
This is an optional step. If you no longer need the temporary database and the certificate, then you can just drop them using the following code:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyEncryptedDB_Temp'; GO USE master; GO DROP DATABASE MyEncryptedDB_Temp; DROP CERTIFICATE MyTempTDECert;