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

by

Troubleshooting Guide: SQL Server Error 9730

Why This Error Happens

Error 9730 occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal, but that principal’s ID no longer maps to a valid server-level principal. This typically happens because the login or user associated with the certificate was dropped, renamed, or orphaned after the Service Broker conversation (dialog) was initiated. It can also occur after a database restore or migration where principal SIDs become mismatched between the database and the server’s master database.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

  • Run the following query against the affected database to cross-reference the principal ID from the error message:
SELECT * FROM sys.database_principals WHERE principal_id = <Id_from_error>;
  • Compare this against sys.server_principals to confirm the mismatch:
SELECT * FROM sys.server_principals WHERE sid = (SELECT sid FROM sys.database_principals WHERE principal_id = <Id_from_error>);
  • If no matching row exists in sys.server_principals, the principal is confirmed orphaned.

Step 2: Re-map or Re-create the Server Login

  • If the login still exists but the SID is mismatched (common after restore/migration), fix it using ALTER USER:
ALTER USER [UserName] WITH LOGIN = [MatchingLoginName];
  • If the login was fully deleted, re-create it with the original SID using:
CREATE LOGIN [LoginName] WITH PASSWORD = 'StrongPassword123!', SID = 0x0123456789ABCDEF;
  • You can retrieve the original SID from sys.database_principals if it was captured before the login was dropped.

Step 3: Re-associate the Service Broker Certificate

  • Navigate to the user that owns the Service Broker certificate and re-issue the certificate binding:
USE [YourDatabaseName];
ALTER CERTIFICATE [CertName] WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'YourPassword');
  • If the certificate was tied specifically to the dropped user, drop and re-create it under the newly fixed principal:
DROP CERTIFICATE [CertName];
CREATE CERTIFICATE [CertName] AUTHORIZATION [FixedUserName]
   WITH SUBJECT = 'Service Broker Certificate';

Step 4: Restart the Service Broker and Clear Conversations

  • End any stuck or hung conversation endpoints that triggered the original error:
END CONVERSATION 'conversation_handle' WITH CLEANUP;
  • Restart the SQL Server service via services.msc on Windows Server 2025, or run:
Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

  • To verify the fix, re-run the query in Step 1—the principal ID should now correctly resolve to an active server principal with no NULL results.
  • If the error persists, check the SQL Server Error Log (ERRORLOG file, typically located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\) for any additional Service Broker (Event ID 9700-series) errors that may indicate a deeper certificate chain corruption requiring a full master key backup/restore.