none
Use Broker with SQL Server AlwaysOn RRS feed

  • Question

  • Hello,

    I have a SQL Server 2016 standard editon with AlwaysOn  setup.
    I was asked to enable the broker. In order to do that I had to remove the database from AlwaysOn AG group , enable the broker and add the database back to AG group.
    ALTER DATABASE [NameOftheDatabase] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

    After the broker was enabled I start getting the following errors :

    The query notification dialog on conversation handle '{40DD07AB-2EC3-E811-A2D4-005056981582}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.

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

    Error: 28054, Severity: 11, State: 1.


    I found the following workaround:

    disabling the dialog security (using encryption = off) or create a master key.

    Or

    ALTER DATABASE YouDatabaseName SET TRUSTWORTHY ON

    I would like to know if this will affect my AlwaysOn setup and if is going to stop the errors.

    Thank you.

    Wednesday, October 3, 2018 3:19 PM

All replies

  • Making the database TRUSTWORTHY is nothing you should do lightly, because it can introduce a security vulnerability. If there are users in the database that have elevated permissions on database level, but that should not have it on server level, this may permit the user to elevate their permissions to server level and become sysadmin.

    I don't want to answer the rest of the question, because I don't know what you use Service Broker for, and if you used if before you set NEW_BROKER. The correct answer is likely to depend on those details.


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

    Wednesday, October 3, 2018 9:55 PM


  • I found the following workaround:

    disabling the dialog security (using encryption = off) or create a master key.

    Or

    ALTER DATABASE YouDatabaseName SET TRUSTWORTHY ON

    I would like to know if this will affect my AlwaysOn setup and if is going to stop the errors.

    Of the options you listed, creating a DMK (Database Master Key) looks to be the safest one. 

    Use <YourDatabase>
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
    GO

    If you do decide to go the route of DMK, make absolutely sure to back it up by running the following statement. keep in mind, the password which you will be using to encrypt the backup of DMK should be different from the password that actually encrypts the DMK (the one in the above statement).

    BACKUP MASTER KEY TO FILE = 'D:\DMKbackup\DBMasterKey'
    ENCRYPTION BY PASSWORD = 'SomeOtherPassword'

    AlwaysOn Availability Groups 2016 supports and interoperates with Service Broker so there should be no issues with that. 

    As with any configuration changes, try in a lower environment before doing anything in prod. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, October 4, 2018 2:45 AM