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'
I can't restore the master key
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?

