Microsoft SQL Server – Error – Service Broker needs to access the master key in the database

Error

Reviewing MS SQL Server Error Log and found:

Service Broker needs to access the master key in the database ‘database-name’;.
Error code:26. The master key has to exist and the service master key encryption is required:

Resolution

Outline

  1. Change Database Context to Identified Database
  2. Review existing Keys
  3. Create existing symmetric key

 

Steps

Change database context to identified database


Use [database-name];

Review existing keys

Select * 
from   sys.symmetric_keys;

Create new key

Syntax:


if not exist
(
    select 1
    from   sys.symmetric_keys
 )
 begin

     create master key 
          encryption by password = 'password';;

 end

Sample:


if not exist
(
     select 1
     from   sys.symmetric_keys
)

begin

     create master key 
          encryption by password = '9181671626514JAMES';

end

References

  1.  Database Engine How-to Topics > Administration How-to Topics  > Encryption How-to Topics
    • How to create a master key
      Link
  2. Docs /SQL / Relational databases / Security / Encryption/
    • Create a database master key
      Link

2 thoughts on “Microsoft SQL Server – Error – Service Broker needs to access the master key in the database

  1. thanks Dan,
    i have created a script, like this:
    {
    Use AdventureWorks2008R2
    select * from sys.symmetric_keys;
    if @@ROWCOUNT = 0
    Create Master Key encryption by password = ‘9181671626514JAMES’;
    }
    antonio
    Dacks – Japan

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s