Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted March 1, 2018

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


How to Send a TDE Encrypted Backup to Someone Outside Your Organization

By Greg Larsen

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:\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

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:\temp\MyTempTDECert.cer'
WITH PRIVATE KEY (file='C:\temp\MyTempTDECert.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:\temp\MyEncryptedDB_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:\temp\MyEncryptedDB_Temp.bak
C:\temp\MyTempTDECert.cer
C:\temp\MyTempTDECert.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;

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.