Troubleshooting Guide: SQL Server Error 9726 — Remote Service Binding Dropped

Why This Error Happens

Error 9726 occurs when SQL Service Broker automatically drops a remote service binding because the certificate or user account associated with it has been dropped or altered, breaking the trust relationship needed for encrypted remote communication. This commonly happens after a master key regeneration, certificate expiration, or when the associated database user is deleted without first removing dependent Service Broker objects. It can also surface during database restores or mirroring failovers where certificate metadata becomes out of sync between the local and remote databases.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Remote Service Binding

• Run the following query to locate the binding ID referenced in the error:
  SELECT * FROM sys.remote_service_bindings WHERE remote_service_binding_id = <%d>;
• Cross-reference the principal_id and service_contract_name to determine which database user and certificate were originally tied to this binding.
• Check SQL Server Error Logs (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp to correlate with recent certificate or user changes.

Step 2: Verify Certificate and User Integrity

• Confirm the certificate still exists by running:
  SELECT * FROM sys.certificates WHERE name = 'YourCertificateName';
• If the certificate was dropped, re-import it from backup using:
  CREATE CERTIFICATE YourCertificateName FROM FILE = 'C:\Certs\YourCertificateName.cer';
• Ensure the associated database user (mapped to the certificate) still exists using SELECT * FROM sys.database_principals WHERE type = 'C';

Step 3: Recreate the Remote Service Binding

• Drop any residual broken binding reference (if still partially present):
  DROP REMOTE SERVICE BINDING YourBindingName;
• Recreate the binding, pointing to the correct user and remote service name:
  CREATE REMOTE SERVICE BINDING YourBindingName TO SERVICE 'RemoteServiceName' WITH USER = YourCertUser;
• Validate that the SERVICE BROKER GUID on both local and remote databases matches using SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDBName';

Step 4: Restart Service Broker and Validate Endpoint Configuration

• Restart Service Broker on the affected database:
  ALTER DATABASE YourDBName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (only if a full reset is acceptable, as this changes the broker GUID)
• Alternatively, re-enable Broker without resetting GUID:
  ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check that the TCP endpoint for Service Broker is active on port 4022 (default) via SELECT * FROM sys.tcp_endpoints WHERE type = 4;

Alternative or Verification Steps

• Verify the fix by sending a test message through the Service Broker conversation and confirming it arrives without triggering Error 9726 in the SQL Server Error Log.
• If the issue persists after recreating the binding, check Windows Firewall rules on Windows Server 2025 to ensure port 4022 is not blocked, and confirm the SQL Server service account has appropriate permissions on the certificate’s private key file in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.