SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Often, these ‘clones’ are copies of production databases, which contain sensitive data that needs to be protected from unauthorized access.
Transparent Data Encryption (TDE) is one technique that many organizations have implemented in order to protect their sensitive data. It encrypts the data at rest, both in the database files and backups. Since SQL Clone copies data from either a live database or backup, does it work with TDE?
Let’s find out with a clone from a live database.
Enable TDE for a database
The process for setting up TDE on a database is fairly simple. There is a good article and description of TDE at SQLServerCentral, but we’ll show the basic steps here.
I have a simple database called
TDE_Primer. This database is unencrypted right now, so let’s protect it with TDE.
I’ll start by creating a Database Master Key (DMK) in the master database that will protect my certificate. This step can be skipped if a DMK already exists.
-- begin encryption setup
-- from http://msdn.microsoft.com/en-us/library/bb934049.aspx
-- create master key for master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This'
Next, I’ll create a certificate, which will protect the Database Encryption Key (DEK) in my
TDE_Primer database. I also perform a backup of this certificate, encrypting the private key before inclusion in the backup, as I’ll need this certificate for other instances on which I might potentially restore my TDE-encrypted database.
-- create certificate to secure TDE
CREATE CERTIFICATE TDEPrimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';
-- backup TDE cert
BACKUP CERTIFICATE TDEPRimer_CertSecurity
TO FILE = 'tdeprimer_cert'
WITH PRIVATE KEY (
FILE = 'tdeprimer_cert.pvk',
ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
Now I change to the
TDE_Primer database, create the DEK, and then encrypt the database.
-- Create DEK
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDEPrimer_CertSecurity;
ALTER DATABASE TDE_Primer
SET ENCRYPTION ON;
Once this completes, I check the encryption status. Note that I’ve included
tempdb in my query, because
tempdb is encrypted on any instance where TDE is enabled in any database.
Now let’s make a SQL Clone.
Creating a data image from an encrypted database
The first step to creating a clone of a database is to create a data image. When I choose to create an image, I get the choice of using a live SQL Server database or a backup. In this case, let’s use a live SQL Server database.
Next, I choose the SQL Server where my database is located, and then select the database. You can see below that I’ve chosen my
I choose a location where the image will be stored. This needs to be a location that all instances that will create clones can access. For this test, I’ll use a local drive, but a network share can be used as well.
Lastly I give the image a name (
TDE_Primer), double check the settings, and click Create Image. I’ll get a progress bar while the image is built from the database.
Once that’s done, I can see my image on the SQL Clone page.
So far, so good. The image isn’t mounted on a SQL Server yet, so I can’t check anything right now. Let’s create a clone. Firstly, I’ll create one on the same SQL Server instance, which should work as the certificate exists, and then I’ll create a clone on another instance.
Create a clone on the same SQL Server instance
To create a clone, I click CLONE on the page in Figure 5. I then get a dialog box asking me to choose a SQL Server instance and give the new, cloned database a name.
I confirm my choices by clicking NEXT, and then clicking CLONE in the Confirm and create clone dialog box.
The process takes seconds and I’ll see a clone listed below my image.
Note the clone is only 48MB in size while the image is 106MB. That 48MB size doesn’t change much, unless you make changes directly to the clone database, even if my image size were 1TB.
This worked fine, and if I check SSMS, I can see that my database clone exists and is marked as being encrypted.
Create a clone on a separate instance
One of the great advantages of SQL Clone is that I can have copies of databases on other instances, allowing each developer or tester to have their own, independent copy of the database without all the storage for the database. In this case, each clone would use only 48MB for each copy unless you make changes to that clone database.
Let’s create another clone, this time on a separate instance. I’ll click CLONE again and select a different instance.
I’ll give this clone the same name, though this doesn’t matter. The name just needs to be unique amongst all databases on this instance. Once I click CLONE on the following dialog box, the process begins, but I get a new result. In the dashboard, I see this:
There is an error at the top of the screen. If you’ve practiced restoring your TDE databases on various servers, I suspect you’ve run into this error at some point. The data image contains the Database Encryption Key (DEK), but on this new instance there is no certificate to protect the DEK and so SQL Server cannot decrypt the DEK, and thus, cannot read data in the database. Let’s fix that.
All you need to do is copy the database and certificate backups created at the beginning of this walkthrough over to the new instance, in preparation for the restore. You’ll also need a need a DMK on this instance, though it can be different from the DMK on the first instance. Note, in the lower right corner of the window of the image below, that I’m now connected to the new instance.
Now let’s create the new clone again. I’ll clear the error and go through the process with the same choices. This time the clone is created successfully.
If I check SSMS, I can see the clone, and it is encrypted. This shows the is_encrypted flag for
tempdb is not set, though the encryption state is set to 3 (encrypted). I suspect this is some artifact of TDE not being explicitly enabled.
This shows that if you’ve properly deployed the certificates protecting your DEK, you can easily use SQL Clone with your TDE-encrypted databases.
In my next post, I look at how SQL Clone works with backups of TDE-encrypted databases and the additional steps you need to take. It’s well worth reading, because then you’ll have a complete picture of how SQL Clone works with TDE.
Also in Blog
Implementing Agile working practices reminds me of Communism – if we just did it properly, everything would work perfectly! All projects would be a dream to work on, all deliveries would be early, t...
Also in Database DevOps & DLM
Visual Studio 2017 is released today, and its new features like Live Unit Testing and configurable code style rules have already been grabbing the headlines. But inside the Visual Studio 2017 installe...
Also in Redgate products
In this article, I'll be showing you how to automatically compare the schema of two versions of the same database, and then subsequently deploy to the target database any differences detected in the s...
Also about Database DevOps
When developing a database as a team, most team members would generally prefer to work with their own, isolated copy of the database, rather than work on a shared development database.
Up to now, how...
Also about SQL Clone
Competition time! To celebrate the launch of SQL Clone, we're giving away 25 Amazon Echo Dots and 5 SQL Clone licenses.
We had a bit of fun creating a skill for Alexa that provisions databases using ...