Backing Up Transparent Database Encryption Keys

Over the last couple of weeks we’ve had an overview of Transparent Database Encryption (TDE) followed by a scripted example of the process. In both of these posts I’d mentioned the importance of backing up the keys and certificates which we’re using for the encryption.

In this post we get to looking at this key step in the process.

Why do we need to backup

When we implement TDE onto one of our databases the data is encrypted at rest. This means that if the underlying files are taken the certificate would need to be present on a new environment before that data could be attached and read.

This protection does give some piece of mind however if the OS has been compromised there may be other issues at play. Its more likely that backups may be compromised as they’ll typically be external to the environment such as on a file share, network location, etc.

This is a handy feature of TDE in that the backups themselves also get encrypted by the same certificate when taken. Of course this is a double edged sword – yes, we know that the files can’t be taken and restored on someone else’s server without the certificate – but they also can’t be restored on ours if we rebuild or restore an environment.

This is another reason why backups are key to providing a robust data platform for the teams we support.

Where to keep your backups

Before we get started its worth taking a moment to consider where you want to keep your backups. In the same way that you store your database backups to a location which is fit for purpose – secure, resilient, recoverable, etc. – the same considerations need to be given to your keys. Clearly the appropriate way will differ on your business and continuity processes.

Personally I see resilient storage as key for this. If you have an issue with a corrupt backup you may well be able to restore to the prior day or week and work around the issue, however if you can’t recover your keys or certificates then you won’t be able to recover any of your encrypted backups. If you have facilities available to utilise a vault or privileged account tool managed by a security team then these can be good options and let the responsibility for those sit in a more appropriate place.

Funny story but I’ve actually seen a PAM tool with a SQL Server backend which has the database encrypted by a certificate that is stored in the PAM tool. What was the recovery approach for the environment – chicken or egg first?

Wherever they may reside they should be secure but accessible (a contradiction indeed). They should be safe from anyone who may want them but available to those who would need them. It should also be documented and shared with anyone who may need to utilise them and clearly laid out how they can be used or restored – in an emergency you don’t want to have to cobble some fresh scripts together, you want a run-book ready and waiting.

Backing up the Database Master Key

Now that we’ve established where we’re going to secure our backups let’s get to backing something up shall we. As we covered previously the master key is required to encrypt the certificates which will be created on the instance so this is something you’ll likely want to back up. Let’s start with that.

For this key we’ll extract a single file which will be secured with a password. The command will look a little like this – obviously replace the password with something a little more personal:

BACKUP MASTER KEY TO FILE = 'D:\KeysAndCertificates\DatabaseMasterKey'
ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';

This is the straight forward part and it’s also the backup you’ll be least likely to need in the future. Typically its the certifications encrypting the databases which you’ll be looking to restore – so lets get on with this.

Just to note that as always you can find further details on this command in the online documentation.

Backing up the encryption Certificate

Now that we’ve got the master key backed up for the instance we need to take care of the certificate/s which are being used across the various databases. We can find the certificates which are in use and when they were last backed up using the query below:

SELECT
    c.[name] [CertificateName],
	c.pvt_key_last_backup_date [LastBackedup],
    COUNT(dek.database_id) [Databases]
FROM
    master.sys.dm_database_encryption_keys dek
    INNER JOIN master.sys.certificates c
        ON dek.encryptor_thumbprint = c.thumbprint
GROUP BY
	c.[name],
	c.pvt_key_last_backup_date;

Now that we know which certificates need backing up – in our case only MyFirstCertificate from our previous exercise – then we’ll need to run the backup operation for each of these.

This time we’ll produce two files – a certificate and a private key – with the private key being encrypted by another password. It’s good practice to make sure this password differs to your master key and also other certificates which you’re backing up. This is another reason why it’s helpful to have a tool designed to store these types of backups rather than various files and passwords being strewn over file shares.

When its time to backup our certificate the command will look a little like the following:

BACKUP CERTIFICATE [MyFirstCertificate]
TO FILE = 'D:\KeysAndCertificates\MyFirstCertificate.cer'
WITH PRIVATE KEY (
	FILE = 'D:\KeysAndCertificates\MyFirstCertificate.pvk',
	ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>');

Again the online documentation has more details if you’d like to delve further.

Wrap up

Here we’ve looked at how we can backup our certificates and keys to help us provide a recovery path if we need to restore these to different environments in the future. We’ve also stressed the importance of identifying an appropriate storage location for these which is resilient and secure so we can be confident in the security of our data.

Now that we have our backups available we’ll follow up by looking at the process to restore an encrypted database into another environment,