We're moving from an EKM provider to local TDE.
We ran through these queries on our test server and everything went as expected.
USE database1;
ALTER DATABASE database1 SET ENCRYPTION OFF;
USE database1;
DROP DATABASE ENCRYPTION KEY;
use master;
select * from sys.asymmetric keys;
USE master;
DROP ASYMMETRIC KEY key_1;
USE master;
ALTER CRYPTOGRAPHIC PROVIDER KeyProvider_1 DISABLE;
When running these on our production server everything went as expected, except that one DB dropped into "Recovery Pending" after disabling the provider.
We re-enabled the provider, and attempted to recreate the asymmetric key:
use master;
create asymmetric key key_1 from provider KeyProvider_1 with provider_key_name = 'EKM_KEY', creation_disposition = open_existing;
But the resulting thumbprint didn't match the one required to bring the DB online. We restored a backup of the master database to the test server, the alias with the proper thumbprint appears when running:
select * from sys.dm_cryptographic_provider_keys(provider_id);
We then copied "database_1" to the test serve and attempted to attach it there, but it throws a different error: Msg 15466, Level 16, State 1, Line 1 An error occurred during decryption.
Running this on the production server give a result of 1 = Unencrypted for database_1:
select*from sys.dm_database_encryption_keys
My thought is that the database encryption key wasn't dropped in the production environment, but the alias was and now the database key is encrypted with an alias that doesn't exist.
Ideally I could just add the asymmetric key back but I've been unable to generate a match. What factors are considered when creating an asymmetric key from an EKM provider? It appears that changing the alias name doesn't affect the result.
I've got plenty of time to test theories as the full image restore has an ETA of ~4days.
Much appreciation for any input. Thank you!