10 February 2017
10 February 2017

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

You may also like

  • Webinar

    Does your current provisioning process meet regulatory requirements?

    DevOps practices, applied to the database, aim to allow organizations to deploy databases at the frequency they need, without introducing an unrealistic administration burden, and without compromising that organization’s compliance with data privacy or security regulations. In this webinar, Product Manager Richard Macaskill explains how to incorporate Redgate’s new SQL Provision into your Database DevOps

  • Article

    How to create and refresh development and test databases automatically, using SQL Clone and SQL Toolbelt

    In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build, when there is a proliferation of copies, and the database is big. This article illustrates a solution, showing how

  • Article

    New permissions features brings access control to SQL Clone

    Ever since its launch, SQL Clone has been attracting waves of interest. By allowing SQL Server databases or backups to be cloned in seconds, as well as saving up to 99% of disk space, it removes the burden of database provisioning. What about access, though? Let’s say you have a mixed team of database developers,

  • Article

    Masking Data in Practice

    Even small extracts of data need to be created with caution, if they are for public consumption. Sensitive data can 'hide' in unexpected places, and apparently innocuous data can be combined with other information to expose information about identifiable individuals. If we need to deliver an entire database in obfuscated form, the problems can get harder. Phil Factor examines some of the basic data masking techniques, and the challenges inherent in masking certain types of sensitive and personal data, while ensuring it still looks like the real data, and preserving its referential integrity, and distribution characteristics.

  • Webinar

    Minimizing the impact of data breaches in dev and test databases

    Data privacy regulations make it essential to implement controls and processes that protect personal data and guard against data breaches. Join Microsoft MVP Steve Jones for a webinar on how to minimize the impact of data breaches in SQL Server dev and test databases.

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant