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

by

Troubleshooting Guide: Error 8470 – “Remote Service Has Been Dropped”

Why This Error Happens

Error 8470 in SQL Server occurs when a remote service binding used for Service Broker conversations is dropped, either manually or as a side effect of a database restore, detach/attach operation, or security configuration change. This typically happens when the certificate or route associated with the remote service no longer matches what the master key or endpoint expects, causing SQL Server to invalidate the binding. Corrupted Service Broker metadata or mismatched SPNs between endpoints can also trigger this condition.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is enabled.
  • If is_broker_enabled returns 0, enable it with ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Recreate the Remote Service Binding

  • Query existing bindings using SELECT * FROM sys.remote_service_bindings; to identify the dropped entry.
  • Drop any stale binding with DROP REMOTE SERVICE BINDING BindingName;
  • Recreate it using the correct certificate: CREATE REMOTE SERVICE BINDING BindingName TO SERVICE 'TargetServiceName' WITH USER = CertificateUser;

Step 3: Validate Certificates and Master Key

  • Check that the database master key exists by running SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
  • If missing, recreate it: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
  • Ensure the certificate used for the remote binding has not expired by checking SELECT name, expiry_date FROM sys.certificates;

Step 4: Restart SQL Server Service and Clear Broker Cache

  • On Windows Server 2025 / Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER), and restart it.
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force
  • This forces Service Broker to reinitialize routes and bindings cleanly.

Alternative or Verification Steps

  • Confirm the fix by sending a test message through the affected Service Broker queue and checking sys.transmission_queue for errors—an empty result set indicates successful delivery.
  • If the issue persists, drop and recreate the entire Service Broker route and conversation endpoint using CREATE ROUTE with the correct ADDRESS and SERVICE_NAME values, then re-test the conversation.