Alzdba, Thanks for your response.
Here is the output:
select MKP.*
, D.name as DbName
from sys.master_key_passwords MKP
inner join sys.credentials C
on MKP.credential_id = C.credential_id
inner join sys.database_recovery_status DRS
on MKP.family_guid = DRS.family_guid
inner join sys.databases D
on DRS.database_id = D.Database_id
;
No rows returned.
Select @@version
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
When I restore the db, the decryption works fine. But when I drop the Master key and recreate the key it doesnt work, returns NULL. Incase a data corruption occurs or the certificates/symmetric keys/master key gets dropped by mistake, we want to provide our client with an approach for the decryption. I'm trying to reproduce the scenario on our SQl 2005 database, after dropping symmetric keys, certificates, master key on the restored db, I restored the master key from the Prod db then created certificates and symmetric keys. I opened the symmetric key and the master key in the session and the decrypt output returns NULL for the existing values. The application team wants to know if it is possible to decrypt the existing data when the keys are dropped & restored.
Please give me your suggestions. Sorry if I'm confusing this.