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

by

Troubleshooting Guide: SQL Server Error 9728

Why This Error Happens

This error occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal ID that no longer exists in the sys.database_principals table. This typically happens when a user or role associated with a Service Broker conversation endpoint is dropped from the database while active dialogs or routes still reference that principal’s security context. The mismatch between the cached conversation metadata and the current principal ID lookup table triggers the failure, often surfacing after user cleanup scripts, migrations, or orphaned user remediation tasks.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

  • Query sys.conversation_endpoints to find active or dying conversations referencing broken principal links.
  • Run: SELECT conversation_handle, principal_id FROM sys.conversation_endpoints WHERE is_initiator = 1;
  • Cross-reference the returned principal_id against sys.database_principals using: SELECT * FROM sys.database_principals WHERE principal_id = <ID_from_above>;
  • If no matching row returns, the principal has been dropped and confirms the root cause.

Step 2: End the Affected Conversations

  • Since the backing certificate/principal no longer exists, the conversation cannot be cleanly closed and must be forcibly ended.
  • Run: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
  • Repeat for each orphaned handle identified in Step 1.
  • If WITH CLEANUP fails due to lock contention, restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Re-provision the Security Certificate and Principal

  • Recreate the database user/principal that was dropped, ensuring the same name is used if legacy code references it explicitly.
  • Run: CREATE USER [BrokerServiceUser] WITHOUT LOGIN;
  • Re-associate and re-issue the certificate binding for Service Broker authentication.
  • Run: CREATE CERTIFICATE BrokerCert AUTHORIZATION [BrokerServiceUser] WITH SUBJECT = 'Service Broker Certificate';
  • Re-grant CONNECT permissions on the relevant Service Broker endpoint to the newly created principal.

Step 4: Validate Service Broker Configuration

  • Check that the is_broker_enabled flag is set to 1 for the database in question.
  • Run: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
  • If disabled, re-enable it via ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Alternative or Verification Steps

  • Verify the fix by re-running the original query/application action that triggered Error 9728; check the SQL Server Error Log (via Event Viewer > Applications and Services Logs > MSSQLSERVER) to confirm Event ID 9728 no longer appears.
  • If the error persists after certificate re-provisioning, consider a full Service Broker queue reset by dropping and recreating the affected queue and service objects, as residual metadata corruption in sys.transmission_queue can sometimes survive a simple cleanup.