Hurdles of Encryption – Refresh X from Y

In short, sys.dm_database_encryption_keys allowed to see the encryption state and I found the system was in progress of decrypting the database.

The hurdle originated while trying to restore encrypted database backups generated by Prod svr(s) to Dev svr(s). The lower env needed the Prod keys to decrypt the Prod backup files. While trying to restore the Prod keys I ran into the errors below because there was another existing database in the Dev svr that was encrypted with SQL Server TDE.

drop master key
go
drop certificate [CRMLTOP_9ef5e895stn536226c2c175a4a79cvw]
go

Errors:

Msg 15580, Level 16, State 1, Line 84
Cannot drop master key because certificate
'CRMLTOP_9ef5e895stn536226c2c175a4a79cvw' is encrypted by it.

Msg 3716, Level 16, State 15, Line 3
The certificate 'CRMLTOP_9ef5e895stn536226c2c175a4a79cvw' cannot
be dropped because it is bound to one or more database encryption key.

In effort to replace the master key, I had to turn off TDE on the encrypted database. This is also under Database Properties, Options, Encryption Enabled was set to True, click drop down and choose False, then click OK to apply.

Prepare to wait, because it has to process through the entire database. Depending on how large, in my case was approx. 7GBs, it took about an hour to decrypt.

alter database [CRMLTOP] set encryption off;

DatabasePropertiesEncryptionEnabled

Here’s the jewel – to see the encryption state run this query:

select db_name(database_id) as DatabaseName
	,encryption_state
	,encryption_state_desc = case encryption_state
		when '0'
			then 'No database encryption key present, no encryption'
		when '1'
			then 'Unencrypted'
		when '2'
			then 'Encryption in progress'
		when '3'
			then 'Encrypted'
		when '4'
			then 'Key change in progress'
		when '5'
			then 'Decryption in progress'
		when '6'
			then  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)'
		else 'aliens' --just incase.
		end
	,percent_complete
	,create_date
	,key_algorithm
	,key_length
	,encryptor_thumbprint
	,encryptor_type
from sys.dm_database_encryption_keys

DatabaseEncryptionStateAdditionally, I was able to see the encrypted database was in use by system spids (<50).

select * from sys.processes where dbid=db_id('CRMLTOP')

After the decryption finished I was able to remove the encryption key for the conflicting database and refresh my master keys for the required restore.

use [CRMLTOP];
go
drop database encryption key;
go
use [master]
go
drop certificate [CRMLTOP_9ef5e895stn536226c2c175a4a79cvw]
go
drop master key
--drop certificate [ProdCertBackupsPK] --for later use.

Lastly, as a suggestion, its good to drop the prod certificate keys from the dev server to avoid the risk of compromising them.

Enjoy,
Hiram

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s