1

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?

  • 1
    Question: 1) I thought we discussed there were no secrets so the DMK could be dropped? 2) If you don't know the actual password and you're not running it on the original server, regenerate will orphan secrets. 3) If you don't care about #2, why not just do #1? – Sean says Remove Sara Chipps May 20 at 19:29
  • @SeanGallardy I need to regenerate the master key but I cannot afford to lose anything in this database – Marcello Miorelli Jul 2 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.