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

by

Troubleshooting Guide: Error 8523 — PREPARE TRAN Statement Not Allowed on MSDTC Transaction

Why This Error Happens

Error 8523 occurs when SQL Server 2025 attempts to execute a PREPARE TRANSACTION statement on a transaction that has already been escalated to or started under the Microsoft Distributed Transaction Coordinator (MSDTC). This happens because PREPARE TRAN is a Transact-SQL syntax reserved for local transactions being promoted manually, and it is fundamentally incompatible with transactions already governed by the two-phase commit protocol managed by MSDTC. Typically, this root cause traces back to the MSDTC service configuration, network DTC access settings, or an application mixing local transaction syntax with distributed transaction contexts.

Step-by-Step Resolution Guide

Step 1: Verify and Restart the MSDTC Service

  • Open Services (services.msc) on Windows Server 2025 / Windows 11.
  • Locate Distributed Transaction Coordinator and confirm the status is “Running.”
  • Right-click and select Restart to clear any stale transaction states.
  • Alternatively, run in an elevated Command Prompt: net stop msdtc && net start msdtc.

Step 2: Enable Network DTC Access

  • Open Component Services (dcomcnfg.exe).
  • Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
  • Right-click Local DTC, select Properties, then go to the Security tab.
  • Ensure Network DTC Access, Allow Remote Clients, Allow Inbound, and Allow Outbound are checked.
  • Set Transaction Manager Communication to “No Authentication Required” only in trusted internal network environments, otherwise use “Mutual Authentication Required.”
  • Click OK and allow the service to restart automatically.

Step 3: Review Application Transaction Logic

  • Check whether the application or stored procedure explicitly calls PREPARE TRAN after a BEGIN DISTRIBUTED TRANSACTION has already been issued.
  • Remove or refactor any manual PREPARE TRAN calls when MSDTC is already coordinating the transaction, since MSDTC handles the prepare phase internally.
  • If using .NET TransactionScope or System.Transactions, ensure the transaction isn’t being escalated unexpectedly due to multiple connections or resource managers.

Step 4: Check SQL Server Configuration for Distributed Transactions

  • In SQL Server Management Studio (SSMS), run: EXEC sp_configure 'remote proc trans';
  • Confirm the value is correctly set based on whether distributed transactions are intended (1 for enabled).
  • Run RECONFIGURE if any changes are made.
  • Validate that Network Access for the SQL Server instance is enabled under SQL Server Configuration Manager > SQL Server Network Configuration.

Step 5: Validate Firewall and Port Settings

  • Ensure port 135 (RPC) and the dynamic DTC port range are open in Windows Defender Firewall on both the SQL Server and client machines.
  • Add an inbound/outbound rule for msdtc.exe if blocked.
  • Confirm this on both Windows Server 2025 and Windows 11 machines if the transaction spans multiple nodes.

Alternative or Verification Steps

  • Use the DTCPing tool or run a test distributed transaction between two servers to confirm MSDTC communication succeeds without Error 8523.
  • If the issue persists, bypass PREPARE TRAN entirely by refactoring the transaction to rely solely on BEGIN DISTRIBUTED TRANSACTION / COMMIT TRANSACTION, letting MSDTC manage the prepare phase automatically instead of invoking it manually.