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

by

Troubleshooting Guide: Error 3969 – Distributed Transaction Not Supported in SQL Server Internal Query

Why This Error Happens

Error 3969 occurs when SQL Server internally attempts to execute a query within the context of a distributed transaction that it does not support for that specific operation, most commonly during logon trigger execution. This typically happens because the logon trigger (or a nested process it calls) enlists in a distributed transaction via BEGIN DISTRIBUTED TRANSACTION or implicit escalation through MSDTC, which SQL Server’s internal metadata queries cannot process. Additionally, this can surface due to corrupted session state or lingering transaction contexts left open by poorly structured triggers or stored procedures that fail to commit/rollback properly before the connection is fully established.

Step-by-Step Resolution Guide

Step 1: Audit and Modify the Logon Trigger Definition

  • Navigate to Object Explorer > Server Objects > Triggers in SQL Server Management Studio (SSMS) 2025.
  • Script out the existing logon trigger using sp_helptext [TriggerName] to review the full T-SQL logic.
  • Remove any explicit calls to BEGIN DISTRIBUTED TRANSACTION or linked server queries that force a distributed transaction promotion.
  • If cross-database queries are required, ensure they target databases on the same SQL Server instance to avoid triggering MSDTC.

Step 2: Verify MSDTC and Transaction Settings

  • Open Component Services (comexp.msc) on your Windows Server 2025 or Windows 11 machine.
  • Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
  • Right-click Local DTC, select Properties, and check the Security tab to ensure “Network DTC Access” settings align with your environment (enable only if cross-server transactions are explicitly required).
  • Restart the MSDTC service via PowerShell if changes are made:
  Restart-Service MSDTC -Force

Step 3: Disable Implicit Transaction Escalation

  • In SSMS, open a new query window connected to the affected instance.
  • Check the XACT_ABORT and IMPLICIT_TRANSACTIONS settings within the trigger body; ensure they are not inadvertently set to ON in a way that forces escalation.
  • Run the following command to inspect current session settings if the trigger is still active:
  DBCC USEROPTIONS;
  • If a linked server is involved, modify its configuration to disable promotion of distributed transactions:
  EXEC sp_serveroption 'LinkedServerName', 'remote proc transaction promotion', 'false';

Step 4: Disable and Test the Logon Trigger

  • If the error persists and locks you out of the instance, start SQL Server in a Dedicated Admin Connection (DAC) mode.
  • Use sqlcmd -S servername -A to connect via the admin channel.
  • Disable the problematic trigger temporarily to restore access:
  DISABLE TRIGGER [TriggerName] ON ALL SERVER;
  • Re-enable the trigger only after the distributed transaction logic has been fully removed from the script.

Alternative or Verification Steps

  • Verification: Attempt a standard login via SSMS or sqlcmd after re-enabling the trigger; monitor the SQL Server Error Log (Management > SQL Server Logs) to confirm Error 3969 no longer appears during the connection handshake.
  • Alternative: If the trigger logic absolutely requires cross-server validation, refactor the logic to use a Service Broker asynchronous call instead of a synchronous linked server query, which bypasses the distributed transaction limitation entirely.