SQL Server – Error – “Service Broker needs to access the master key in the database. Error code:32.”

Background

Reviewing SQL Server Instance for performance issue and noted the error message listed below :-

Error

Error Image

errorLog.20181211.1150AM

Error Text


Error: 28054, Severity: 11, State: 1

Service Broker needs to access the master key in the database 'IdfMC'.
Error code:32.
The master key has to exist and the service master key encryption is required.

Troubleshooting

Metadata

sys.databases

SQL

Sample
use [IdfMC]
go

select
           [database]
            = tblSD.[name]

         , tblSD.is_master_key_encrypted_by_server 

         , [currentDB]
            = case
                when (tblSD.[name] = db_name()) then 1
                else 0
              end

from   sys.databases tblSD

Output

sys.databases.before.20181211.1156AM

Output
  1. Table :- sys.databases
    • is_master_key_encrypted_by_server
      • 0 ( Master Key not encrypted )

sys.symmetric_keys

SQL

Sample
use [IdfMC]
go

select *

from sys.symmetric_keys

Output

sys.sysmmetric_keys.20181211.1157AM

Explanation
  1. Table :- sys.symmetric_keys
    • Empty

Remediation

Create Master Key

Outline

  1. Change Contextual Database
    • use [database]
  2. Issue “Create Master Key

SQL

Sample

use [IdfMC]
go

IF NOT EXISTS
(
    SELECT *
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101
)
BEGIN

  PRINT 'Creating Database Master Key'

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Spuvephu3h'

END

Validate

Metadata

sys.databases

SQL

Sample
use [IdfMC]
go

select
           [database]
            = tblSD.[name]

         , tblSD.is_master_key_encrypted_by_server 

         , [currentDB]
            = case
                when (tblSD.[name] = db_name()) then 1
                else 0
              end

from   sys.databases tblSD

Output

sys.databases.before.20181211.1206AM

Output
  1. Table :- sys.databases
    • is_master_key_encrypted_by_server
      • 1 ( Master Key encrypted )

sys.symmetric_keys

SQL

Sample
use [IdfMC]
go

select *

from sys.symmetric_keys

Output

sys.sysmmetric_keys.20181211.1246PM

Explanation
  1. Table :- sys.symmetric_keys
    • Records
      • Name :- ##MS_DatabaseMasterKey##
      • Principal_id :- 1
      • symmetric_key_id :- 101
      • key_length :- 256
      • key_algoritm :- A3
      • algorithm_desc :- AES_256

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s