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

by

Troubleshooting Guide: Error 7870 – “The AFFINITY clause is not supported for endpoints of this type”

Why This Error Happens

This error occurs when a CREATE ENDPOINT or ALTER ENDPOINT statement attempts to apply the AFFINITY clause to an endpoint type that does not support it, such as a DATABASE_MIRRORING or SOAP endpoint, rather than a TSQL or TCP-based endpoint designed for affinity binding. The AFFINITY clause is specifically reserved for binding an endpoint to a particular network interface or port configuration on certain endpoint protocols, and SQL Server 2025 enforces stricter type validation during endpoint creation than earlier builds. This frequently surfaces after a migration script written for an older SQL Server version is replayed against a new Windows Server 2025 instance without updating the endpoint definition syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Endpoint Type Causing the Conflict

  • Run SELECT name, type_desc, protocol_desc FROM sys.endpoints; in SSMS to list all current endpoints.
  • Confirm whether the endpoint flagged in the error is DATABASE_MIRRORING, SERVICE_BROKER, or SOAP — these types reject the AFFINITY clause.
  • Cross-reference the failing script against the exact endpoint name shown in the Error 7870 message text.

Step 2: Edit the T-SQL Script to Remove or Replace the AFFINITY Clause

  • Open the deployment or migration script (commonly located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Install\ or your custom deployment folder).
  • Remove the AFFINITY keyword and its associated port/IP parameters from the CREATE ENDPOINT statement.
  • If network binding is still required, reconfigure it using LISTENER_IP and LISTENER_PORT parameters instead, which are valid for TSQL and TCP endpoint types.
  • Save the script and re-run it against the target instance using sqlcmd -S <ServerName> -i fixed_script.sql.

Step 3: Validate Endpoint Permissions and Firewall Configuration

  • Ensure the SQL Server service account has rights to bind to the specified port by checking Windows Server 2025 Firewall with Advanced Security.
  • Confirm no other service (like a competing SQL instance or IIS) is already bound to the same port using netstat -ano | findstr <PortNumber> in PowerShell.
  • Restart the SQL Server service via Restart-Service MSSQLSERVER after applying the corrected endpoint script.

Step 4: Rebuild the Endpoint from Scratch if Corruption Is Suspected

  • Drop the problematic endpoint using DROP ENDPOINT <EndpointName>;.
  • Recreate it fresh with the correct syntax matching its intended type (e.g., FOR TSQL() without AFFINITY for mirroring endpoints).
  • Re-grant connect permissions with GRANT CONNECT ON ENDPOINT::<EndpointName> TO [LoginName];.

Alternative or Verification Steps

  • Verify the fix by querying sys.endpoints and sys.tcp_endpoints to confirm the endpoint now shows a valid state without triggering Error 7870 on re-execution of the script.
  • If the error persists, check the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for additional context, and consider applying the latest Cumulative Update for SQL Server 2025 in case this is a known parser-level bug for that endpoint type.