• SQL Clone
• Compliant Database Provisioning

SQL Clone and databases protected with TDE – part 1

SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Sometimes, these ‘clones’ are copies of databases that 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.

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.

Now I change to the TDE_Primer database, create the DEK, and then encrypt the database.

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.

Figure 1

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.

Figure 2

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 TDE_Primer database.

Figure 3

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.

Figure 4

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.

Figure 5

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.

Figure 6

I confirm my choices by clicking NEXT, and then clicking CLONE in the Confirm and create clone dialog box.

Figure 7

The process takes seconds and I’ll see a clone listed below my image.

Figure 8

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.

Figure 9

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.

Figure 10

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:

Figure 11

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.

Figure 12

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.

Figure 13

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.

Figure 14

This shows that if you’ve properly deployed the certificates protecting your DEK, you can easily use SQL Clone with your TDE-encrypted databases.

Next Steps

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.

Tools in this post

SQL Clone

Create SQL Server database copies in an instant

Find out more

SQL Provision

Provision virtualized clones of databases in seconds, with sensitive data shielded

Find out more