sql error "Service Broker needs to access the master key in the Database "xxx" Master Key has to exist

7,080

I had the same error on databases that uses the broker that where moved to another server so I'm writing my answer in case it can help someone.

When you move a database that uses the broker with an encryption key, it seems you need to recreate the master key for the broker to be able to use it.

First, open the master key, then recreate it.

OPEN MASTER KEY DECRYPTION BY PASSWORD ='SomeStrongPassword'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD ='SomeStrongPassword'

That solved the problem for me.

Share:
7,080

Related videos on Youtube

RayofCommand
Author by

RayofCommand

Updated on September 18, 2022

Comments

  • RayofCommand
    RayofCommand over 1 year

    I am pretty new to SQL and administration of databases and still learning. We receive one SQL error for quiete a long time, which has as far as we know no effect on our application. So we put that to second priority. Now it starts bothering me and I want to know whats going on.

    Service Broker needs to access the master key in the datasbase "xxx" Error Code:26. The Master Key hast to exist and the service Master Key encryption is required.
    

    I checked our full sql log and found that the error occured out of nothing, I mean no updates installed at that time and no newer version was released of our application. So for me it came "out of nothing" at least I can't see anything now. It's some weeks ago that all started and maybe I can't remember everything.

    How can it be possible that the service broker needs to access the master key and we don't even have keys in our db. we never created one.

    Can anyone explain me how to use the key? I mean creating is ok :

    USE xxx;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxx';
    GO
    

    Any help is upvoted immediately.