Following up after a situation on an important
LIVE server -
where I have the master key backed up,
but the decryption password for that master key backup file is missing.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
RESTORE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' ENCRYPTION BY PASSWORD = 'password' [ FORCE ]
What I am left with is to regenerate the master key:
USE AdventureWorks2012; ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; GO
Before doing that, however,
I did some tests, like the one below:
what I want to do is:
- find out everything that is affected my the master key
- script object definitions, save the data
- drop or regenerate the master key
- re-create or alter any object definitions (including their permissions)
- import back all data
Basically I want to backup everything affected by the master key, then regenerate it and then re-apply all the affected things back to the database
when I run the following queries I don't get any results:
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select * from sys.master_key_passwords ; select * from sys.asymmetric_keys ; select * from sys.crypt_properties ; select * from sys.credentials ;
when I run these ones though, I get results:
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select * from sys.certificates ; select * from sys.key_encryptions ; select * from sys.symmetric_keys ;
I cannot even add the database to the availability group, without sorting the master key first.
how can I find anything dependent on the master key?