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

by

Troubleshooting Guide: Error 9697 – Could Not Start Service Broker

Why This Error Happens

Error 9697 typically occurs when SQL Server cannot initialize the Service Broker endpoint for a specific database, often due to a corrupted or mismatched Service Broker GUID in the database metadata. This can also happen if the master key or certificate used for Service Broker encryption is missing, inaccessible, or if there is a conflict with another database sharing the same Service Broker identifier (common after a database restore or copy operation). Additionally, insufficient permissions on the service account or network connectivity issues on the Service Broker port can prevent proper initialization.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log for Root Cause

  • Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
  • Look for messages immediately preceding or following Error 9697, such as missing certificate errors, GUID conflicts, or permission denials.
  • Alternatively, review the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Verify and Repair the Service Broker GUID

  • Run the following query to check the current Broker GUID and status:
  SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the database was restored or copied and now has a duplicate GUID, regenerate it using:
  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
  • Note: Use NEW_BROKER only if you do not need to preserve existing message queues, as this clears them.

Step 3: Validate the Database Master Key and Certificates

  • Ensure the Service Master Key (SMK) and Database Master Key (DMK) are accessible and not corrupted.
  • Run the following to check for the master key:
  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
  • If missing or corrupted, restore it from backup or recreate it using:
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

Step 4: Confirm Service Account Permissions and Firewall Rules

  • Verify that the SQL Server service account has the necessary permissions on the data and log file directories (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
  • Check Windows Firewall settings on Windows Server 2025 to ensure the Service Broker port (default 4022) is not blocked.
  • Use PowerShell to verify the rule: Get-NetFirewallRule -DisplayName "*SQL Server Service Broker*".

Step 5: Restart SQL Server Service and Re-enable Broker

  • Restart the SQL Server (MSSQLSERVER) service via services.msc or PowerShell: Restart-Service -Name "MSSQLSERVER".
  • Re-enable Service Broker manually if it is disabled:
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Alternative or Verification Steps

  • Verify the fix by querying sys.databases again to confirm is_broker_enabled returns 1 and no further 9697 errors appear in the error log after restarting the service.
  • If the issue persists, consider detaching and re-attaching the database, or restoring from a known good backup where Service Broker was functioning correctly.