Removing Transparent Database Encrypion

We’ve recently been looking at Transparent Database Encryption and how we go about implementing it an dealing with it in our environments. Now the question comes – what if we need to remove it?

Well that’s why we’re here!

Decrypting the database

We’ll be approaching this in the reverse order to how we applied the encryption so the first step for us is to get the database out of an encrypted state. We’ll start by disabling this as follows:

ALTER DATABASE [Blog]
SET ENCRYPTION OFF;  
GO

This is the part which may take a while as the files on disk will get decrypted and need to be rewritten. As with before we can query the DMV to check the database and see the state it is in when the decryption process is working through:

SELECT
	db_name(database_id) [Database],
	c.name [Certificate],
	dek.encryption_state,
	CASE dek.encryption_state
		WHEN 0 THEN 'No encryption key, not encrypted'
		WHEN 1 THEN 'Encryption key present, not encrypted'
		WHEN 2 THEN 'Encryption in progress'
		WHEN 3 THEN 'Encrypted'
		WHEN 4 THEN 'Key change in progress'
		WHEN 5 THEN 'Decryption in progress'
		ELSE 'Who knows?' END [state_desc],
	dek.percent_complete
FROM
	master.sys.dm_database_encryption_keys dek
	INNER JOIN master.sys.certificates c
		ON dek.encryptor_thumbprint = c.thumbprint;

Removing the encryption key

You’ll see when running the query above our database is showing as not encrypted but we’ve still got our key preset.

It’s not just enough to decrypt the database and we’re done. The data may be decrypted however we’ve still got an encryption key for the database which is tied to a certificate. We’ll want to get that removed too.

Fortunately for us it’s a straight forward operation:

USE BLOG;
GO

DROP DATABASE ENCRYPTION KEY;
GO

If we now go ahead and rerun the query above to take a look at the DMV you’ll see the database has disappeared, there’s no encryption being applied any longer.

Removing the certificate

This step is completely optional as we’ve removed the key which tied the certificate and database together so this isn’t strictly needed. Also you may have used the same certificate to encrypt other databases on the environment in which case it will need to remain.

However for completeness let’s look at removing the certificate from the environment for some housekeeping. Once again it’s a straight forward drop command:

USE master;
GO

DROP CERTIFICATE MyFirstCertificate;
GO

Perform a backup

Now that you’ve got the database decrypted you may want to manually perform a full backup followed by a log backup to truncate the transaction log.

The reason for this is that even though the database is no longer encrypted, if you were to try and restore an existing backup, that data would be encrypted with the key which previously existed. If we’d also decided to remove the certificate from the environment it would mean we couldn’t restore the previous backup.

By completing a full and then log backup after removing the encryption we now have a database and (truncated) transaction log which is no longer encrypted and can be backed up and restored as required.

Wrap up

This time out we’ve taken look at removing the encryption which we applied previously for completeness. It’s essentially the same approach we took to encrypt the data but in reverse.

This almost completes the cycle with the encryption however there’s one last topic I want touch on to wrap it up – it’s concerning the expiry of certificate and what we’ll need to do when the time arrives to rotate the encryption key.