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

by

Troubleshooting Guide: Error 575 – LOGON Trigger Returned a Resultset

Why This Error Happens

This error occurs because a DML trigger defined on the LOGON event is executing a statement (such as a SELECT or a PRINT-adjacent operation returning tabular output) that generates a resultset back to the client. SQL Server 2025 strictly enforces that LOGON triggers must be silent processes, since any resultset returned during the authentication phase can interfere with the TDS (Tabular Data Stream) protocol handshake between the client and server. This is typically caused by leftover debugging code (like a stray SELECT statement) or a stored procedure called within the trigger that inadvertently returns data.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Trigger

  • Open SQL Server Management Studio (SSMS) and connect using the Dedicated Administrator Connection (DAC) if standard logons are being blocked (e.g., Admin:YourServerName).
  • Navigate to Security > Server Objects > Triggers in Object Explorer to view all active LOGON triggers.
  • Run the following query to view trigger definitions directly:
SELECT name, OBJECT_DEFINITION(object_id) 
FROM sys.server_triggers;

Step 2: Modify the Trigger to Suppress Resultsets

  • Locate any SELECT statements inside the trigger body that are not assigned to a variable (these implicitly return data to the client).
  • Add SET NOCOUNT ON; at the very beginning of the trigger script to prevent row-count messages from being misinterpreted as resultsets.
  • If the trigger calls an external stored procedure, ensure that procedure also contains SET NOCOUNT ON; and does not use bare SELECT statements for logging purposes.
  • Example fix:
ALTER TRIGGER [YourLogonTrigger] 
ON ALL SERVER 
FOR LOGON 
AS 
BEGIN
    SET NOCOUNT ON; 
    -- Ensure no SELECT statements exist below without INTO or variable assignment
    IF ORIGINAL_LOGIN() = 'sa' 
    BEGIN
        ROLLBACK; 
    END
END;

Step 3: Disable the Trigger via DAC (If Locked Out)

  • If the faulty trigger is preventing all logons (including admin), you must use the DAC.
  • Open a Command Prompt on the Windows Server 2025 host and use sqlcmd to connect: sqlcmd -S ADMIN:YourServerName.
  • Execute the disable command immediately to restore server access:
DISABLE TRIGGER [YourLogonTrigger] ON ALL SERVER;

Step 4: Restart the SQL Server Service

  • Open the Services application (services.msc) on Windows Server 2025.
  • Restart the “SQL Server (MSSQLSERVER)” service to clear any cached connection states related to the previous failed logon attempts.
  • Verify the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm the service started without further Error 575 flags.

Alternative or Verification Steps

  • Verification: Attempt a standard login via SSMS using a non-sysadmin test account to ensure the trigger executes silently and the connection completes without the “Resultset” error popup.
  • Alternative: If modifying the trigger logic is complex, temporarily disable all logon triggers server-wide using sp_configure 'server trigger recursion', 0 and manually audit the trigger via a temporary logging table instead of PRINT or SELECT statements to avoid resultset conflicts entirely.