How to fix Error 9731 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 9731 – Dialog Security Unavailable for Conversation

Why This Error Happens

This error occurs because Service Broker requires a security certificate bound to the database principal to encrypt and authenticate dialog conversations, but no such certificate exists. It typically arises when a database user or principal was created without an associated certificate, or when the certificate was dropped, expired, or never generated during the initial Service Broker setup. This is common in environments where dialog security (as opposed to transport security) is enforced between endpoints but the certificate infrastructure was not fully configured.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database Principal

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
  • Run the following query to find the principal ID referenced in the error:
  SELECT name, principal_id FROM sys.database_principals WHERE principal_id = <Id from error>;
  • Confirm whether this principal already has a certificate by querying sys.certificates.

Step 2: Create a Certificate for the Principal

  • In the context of the correct database, run:
  USE [YourDatabaseName];
  CREATE CERTIFICATE Cert_<PrincipalName>
  AUTHORIZATION [<PrincipalName>]
  WITH SUBJECT = 'Service Broker Dialog Security Certificate';
  • Ensure the AUTHORIZATION clause matches the exact database principal referenced in the error message.

Step 3: Exchange Certificates Between Participating Databases (If Cross-Database/Instance)

  • Back up the certificate using BACKUP CERTIFICATE Cert_<PrincipalName> TO FILE = 'C:\Certs\Cert_<PrincipalName>.cer';.
  • Copy the .cer file to the remote instance/server if the conversation spans multiple databases or servers.
  • On the remote side, create a corresponding certificate for the remote principal using CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\...'.

Step 4: Restart the Conversation

  • Terminate any stuck or orphaned conversations using END CONVERSATION with the relevant handle, or clean up via sys.conversation_endpoints.
  • Re-initiate the BEGIN DIALOG CONVERSATION command from the application or stored procedure to confirm Service Broker can now negotiate security.

Alternative or Verification Steps

  • Verify the fix by querying sys.certificates and confirming the certificate is now associated with the correct principal_id; then re-run the Service Broker message send operation to confirm Error 9731 no longer appears.
  • If dialog security is not a strict requirement for your scenario, consider switching to transport security only by disabling DIALOG SECURITY at the endpoint level, though this is not recommended for production environments requiring message-level encryption.