Service Broker RRS feed

  • Question

  • I just migrated SQL Server 2008 R2 to SQL Server 2017 Standard edition to a different server. I migrated User databases only, not the system databases. Since the migration, I have been getting the following error:

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

    Googling around, I found two potential solutions:

    1. Turn the database property TRUSTWORTH ON
    2. Create a new Master Key.

    I do have the master key in the database. Following is the detail using the SQL script
    SELECT * FROM master.sys.symmetric_keys

    database: master
    name: ##MS_ServiceMasterKey##
    principal_id: 1
    symmetric_key_id: 102
    key_length: 256
    key_algorithm: A3

    database: dbname
    name: ##MS_ServiceMasterKey##
    principal_id: 1
    symmetric_key_id: 101
    key_length: 128
    key_algorithm: D3

    I do not want to turn on the TRUSTWORTHY as it might have security impact.
    How do I resolve this issue? Should I create a new master key for each database affected by this issue? Will it break anything? To create a new master key, do I need the password for the old master key? If I create symmetric master key in database, will it synchronize with master key already in master database?

    Or should I just overwrite the master database with the master database from old server?

    I really hope someone will shed light on this and provide me the least disruptive solution.

    Thank You.

    • Edited by bhaktapur Monday, January 27, 2020 8:14 PM
    Monday, January 27, 2020 8:10 PM

All replies

  • Supposedly the database master key was protected by the service master key on the old server, to the best resolution would be to backup the service master key on the old server and restore it on the new server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 27, 2020 10:36 PM