By Marcello Miorelli


2019-05-20 15:08:33 8 Comments

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?

0 comments

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] SSISDB master key

2 Answered Questions

[SOLVED] Service Master Key inaccessible, SSRS key only one backed up

1 Answered Questions

1 Answered Questions

3 Answered Questions

1 Answered Questions

[SOLVED] Decrypting Previously Encrypted Data Returns NULL

1 Answered Questions

[SOLVED] SQL Server 2012 Service Master Key Regenerate

1 Answered Questions

[SOLVED] How do I allow Service Broker access to Master Key of database?

Sponsored Content