2

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.

I can't open the master key

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:

enter image description here

what I want to do is:

  1. find out everything that is affected my the master key
  2. script object definitions, save the data
  3. drop or regenerate the master key
  4. re-create or alter any object definitions (including their permissions)
  5. 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 ;

enter image description here

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?

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

0 Answers0