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;

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

Additionally, 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