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

by

Troubleshooting Guide: SQL Server Error 9727 — Dialog Security Not Available (No Remote Service Binding)

Why This Error Happens

This error occurs when Service Broker attempts to establish a secure dialog with a remote service, but no REMOTE SERVICE BINDING object exists to define the certificate-based security context for that target service. Service Broker requires an explicit binding that maps a local user (with an associated certificate) to the remote service name in order to encrypt and authenticate the conversation. Without this binding, SQL Server has no way to determine which credentials to use for establishing dialog security, causing the conversation initiation to fail.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker and Certificate Prerequisites

  • Confirm Service Broker is enabled on the database: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • Ensure a master key exists in the database: SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
  • Verify that a certificate has been created and exchanged with the remote server for authentication purposes.
  • Check that the certificate has not expired by querying sys.certificates.

Step 2: Create the Missing Remote Service Binding

  • Identify the local database user associated with the certificate used for outbound authentication (e.g., dbo or a dedicated Service Broker user).
  • Run the following T-SQL on the initiator database:
CREATE REMOTE SERVICE BINDING [RemoteBindingName]
TO SERVICE 'TargetServiceName'
WITH USER = [LocalUserWithCertificate];
  • Replace TargetServiceName with the exact name of the remote Service Broker service (case-sensitive and must match exactly).
  • Replace LocalUserWithCertificate with the database user that owns the certificate paired with the remote endpoint.

Step 3: Validate Certificate Trust Between Instances

  • Confirm the public key certificate from the remote instance has been imported locally using CREATE CERTIFICATE ... FROM FILE.
  • Ensure the certificate on both ends uses matching START_DATE and EXPIRY_DATE values and has not lapsed.
  • Check the SQL Server error log on both instances for related Service Broker handshake failures (Event ID 9719 or 9731 often accompany this issue).

Step 4: Restart Service Broker and Re-Test the Conversation

  • Run ALTER DATABASE [YourDatabaseName] SET NEW_BROKER; only if conversations are stuck and can be safely reset (use with caution in production).
  • Re-send a test message using BEGIN DIALOG CONVERSATION to confirm the binding resolves correctly.
  • Monitor sys.transmission_queue for any residual error descriptions tied to the conversation handle.

Alternative or Verification Steps

  • Run SELECT * FROM sys.remote_service_bindings; to confirm the new binding appears with the correct service_name and user_id mapping.
  • If the error persists, verify firewall rules on Windows Server 2025 / Windows 11 are not blocking the Service Broker endpoint port (default 4022), and confirm the endpoint state is STARTED via SELECT * FROM sys.service_broker_endpoints;.