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

by

Troubleshooting Guide: SQL Server Error 11211 – Service Broker Message Dropped (Permission Denied)

Why This Error Happens

Error 11211 occurs within SQL Server Service Broker when an incoming message targets a database, but the user account associated with the message (often the guest account or a specific login mapped via a Service Broker route) lacks the CONNECT permission on that specific database. This is commonly triggered when the guest user is disabled in the target database, or when certificate-based Service Broker security grants authentication but not actual database-level connection rights. Additionally, if the database was recently restored or migrated in Windows Server 2025 environments, orphaned users or mismatched SIDs can cause the Service Broker endpoint to resolve to a user with revoked access.

Step-by-Step Resolution Guide

Step 1: Identify the Target Database and Message Origin

  • Open the SQL Server Error Log or Event Viewer and locate the full Error 11211 text to extract the Database ID and Message Origin values.
  • Run the following query to convert the Database ID to a readable name:
  SELECT DB_NAME(<Database_ID>) AS TargetDatabase;
  • Note the service name referenced in message origin to confirm which Service Broker endpoint is affected.

Step 2: Verify and Enable Guest Access (If Applicable)

  • Check whether the guest user is enabled in the target database, as Service Broker often relies on this for inter-database communication.
  • Run the following in SSMS (SQL Server Management Studio) connected to the target database:
  USE [TargetDatabaseName];
  GRANT CONNECT TO guest;
  • If organizational policy prohibits enabling guest, proceed to Step 3 to map an explicit user.

Step 3: Map the Service Broker Login to a Database User

  • Identify the login associated with the Service Broker certificate or route using:
  SELECT name, principal_id FROM sys.server_principals WHERE type = 'S';
  • Create a corresponding user in the target database and grant CONNECT permission explicitly:
  USE [TargetDatabaseName];
  CREATE USER [ServiceBrokerLogin] FOR LOGIN [ServiceBrokerLogin];
  GRANT CONNECT TO [ServiceBrokerLogin];
  • Ensure this user is also granted EXECUTE rights on the Service Broker message type/contract if a stored procedure activation is involved.

Step 4: Check for Orphaned Users (Post-Migration Scenarios)

  • On Windows Server 2025 instances where the database was restored from a backup, run the orphaned user detection query:
  EXEC sp_change_users_login 'Report';
  • Remap any orphaned SIDs using:
  EXEC sp_change_users_login 'Update_One', 'ServiceBrokerLogin', 'ServiceBrokerLogin';

Step 5: Restart the Service Broker and Validate Routing

  • Disable and re-enable Service Broker on the target database to force a refresh of internal permission caches:
  ALTER DATABASE [TargetDatabaseName] SET DISABLE_BROKER;
  ALTER DATABASE [TargetDatabaseName] SET ENABLE_BROKER;
  • Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear any cached authentication tokens.

Alternative or Verification Steps

  • After applying permissions, send a test message through the Service Broker queue and monitor sys.transmission_queue for the target database; a cleared queue confirms the message was delivered successfully.
  • If the error persists, verify that the SQL Server Service Account (visible in services.msc) has not been altered, as a changed service identity on Windows 11/Server 2025 can disrupt certificate-based Service Broker authentication entirely.