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:

1] Change database context to identified database



Use <databasename>;

2] Review existing keys


Select * 
from   sys.symmetric_keys;


3] 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:

About these ads

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

Fill in your details below or click an icon to log in:

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