SQL Clone and databases protected with TDE

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.

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.

SQL Clone TDE
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.

SQL Clone TDE 2
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.

SQL Clone TDE 3
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.

SQL Clone TDE 4
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.

SQL Clone TDE 5
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.

SQL Clone TDE 6
Figure 6

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

SQL Clone TDE 7
Figure 7

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

SQL Clone TDE 8
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.

SQL Clone TDE 9
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.

SQL Clone TDE 10
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:

SQL Clone TDE 11
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.

SQL Clone TDE 12
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.

SQL Clone TDE 13
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.

SQL Clone TDE 14
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

Clone SQL Server databases in seconds and save up to 99% disk space.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

SQL Data Mask: now featuring configurable masks

SQL Data Mask is the latest prototype to come out of the Foundry. It copies your database while anonymizing personal data. You can use it to mask your databases right now, free of charge.

What we’v...

Also in Database DevOps & DLM

Using striped backups with SQL Clone

If you're a Redgate SQL Backup customer, occasionally you'll need to convert your SQL Backup (.sqb) files to the native SQL Server backup format (.bak), perhaps to perform native database restores on ...

Also in Redgate products

How to document multiple SQL Server databases using SQL Doc and PowerShell

You can use SQL Doc's command-line parameters to automate database documentation, but when you try to automate the process of documenting a group of databases on a server, they sometimes don't give yo...

Also about Database DevOps

Why people flocked to the SQL Clone live stream event

Live streaming events is something that’s rather new to Redgate, the first one being SQL in the City back in December, which was a great success. So with the launch of our new tool SQL Clone looming...

Also about SQL Clone

Why is my clone so small?

If you have a very large database, up to 2TB in size, SQL Clone will let you copy, or 'clone', that database many times, very quickly, making the full database available in multiple SQL Server instanc...