Post reply

Service Broker & Query Notification

  • May 29, 2016 at 1:46 am

    #310911

    Experts,

    Getting below error logged into my SQL Logs every a few hours.

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

    Message

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

    The below error is logged frequeently

    The query notification dialog on conversation handle '{28F65369-1C25-E611-80D8-005056940E84}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-da2b0db2-2bc7-4f18-9044-4d7059b37d47' because it does not exist.</Description></Error>'.

    Can anyone help me understand this?

    Thanks in Advance.

  • May 30, 2016 at 10:36 pm

    #1882064

    Anyone on this?

  • May 31, 2016 at 11:55 am

    #1882238

    VastSQL,

    I gathered this information from the following link:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5df97b1f-7648-4ab6-8350-d1beb10dd38d/log-files-full-of-service-broker-errors-but-it-works-ok?forum=sqlservicebroker

    Run this statement:

    Select name, is_master_key_encrypted_by_server from sys.databases

    Where name = 'SIGNALR'

    if the value of "is_master_key_encrypted_by_server" equals to "0", it means the database does not have a encrypted master key.

    if there is no encrypted master key, then the error may be hit by the "begin dialog conversation" statement (you can check your sql profiler trace to check".

    "Service Broker dialog security lets your application use authentication, authorization, or encryption for an individual dialog conversation (or dialog). By default, all dialog conversations use dialog security. When you begin a dialog, you can explicitly allow a dialog to proceed without dialog security by including the ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement. However, if a remote service binding exists for the service that the conversation targets, the dialog uses security even when ENCRYPTION = OFF."

    (http://msdn.microsoft.com/en-us/library/ms166036.aspx)

    Workarounds can be disabling the dialog security (using encryption = off) or create a master key. You can find more information in about URL.

    Hope this helps you!

    Jon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply