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

by

Troubleshooting Guide: SQL Server Error 9202 — Invalid Query Notification Subscription Message

Why This Error Happens

Error 9202 occurs when the Service Broker message used to deliver a query notification subscription has been malformed, truncated, or tampered with before reaching the SqlQueryNotificationService-* queue. This is typically caused by an application incorrectly constructing the subscription XML (invalid syntax, missing required elements, or malformed SqlQueryNotificationStoredProcedure-* parameters), an outdated SQL Native Client/ODBC driver generating malformed WATCHDOG headers, or corruption introduced by a mismatched Service Broker dialog between the client and SQL Server 2025 instance. In some cases, this also results from certificate or encryption mismatches on the Service Broker endpoint, causing the message payload to decrypt improperly before validation.

Step-by-Step Resolution Guide

Step 1: Validate the Query Notification Subscription Syntax

  • Open the application code or stored procedure issuing the SUBSCRIBE command via SqlDependency or SqlNotificationRequest.
  • Confirm the query meets notification requirements (two-part table names, no SELECT *, no aggregates without GROUP BY restrictions).
  • Example of a valid minimal query:
  SELECT OrderID, Status FROM dbo.Orders WHERE Status = 'Pending';
  • Reject and rewrite any queries using unsupported functions (e.g., RAND(), NEWID(), subqueries referencing views without SCHEMABINDING).

Step 2: Update SQL Client Drivers and Connection Libraries

  • On Windows Server 2025 / Windows 11, open Control Panel > Programs > Programs and Features.
  • Verify the installed version of Microsoft OLE DB Driver for SQL Server or ODBC Driver 18+ matches compatibility requirements for SQL Server 2025.
  • Download and install the latest driver from the official Microsoft Download Center if outdated.
  • Restart the application pool or service consuming SqlDependency after the driver update.

Step 3: Reset and Rebuild the Service Broker Queue

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following to confirm Service Broker is enabled on the target database:
  SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If disabled or suspect corrupted, reset it safely:
  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
  • Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025.

Step 4: Verify Endpoint and Certificate Configuration

  • Check the Service Broker endpoint state using:
  SELECT name, state_desc FROM sys.service_broker_endpoints;
  • If a certificate mismatch is suspected, regenerate and re-associate the database master key and certificate used for Service Broker encryption.
  • Ensure the SQL Server service account has read access to the certificate store, validated via certlm.msc on Windows Server 2025.

Alternative or Verification Steps

  • Re-run the application’s subscription request and query sys.dm_qn_subscriptions to confirm an active subscription entry appears without errors.
  • If the issue persists, enable Service Broker trace flags (2537, 9023) temporarily to capture detailed diagnostic output in the SQL Server error log, then analyze the malformed message structure before re-testing the subscription.