Logon Storms: Protecting SQL Server From Connection Floods

Connections keep arriving even after the database has run out of useful capacity. Handling logon storms starts with identifying the source, controlling retries, and keeping an administrator route available.

Distinguish Session Growth From Login Rate

During logon storms, a large session count and a high login rate describe different problems. A stable pool can maintain many idle connections without repeated authentication. A broken retry loop can create rapid connection churn while leaving fewer established sessions visible.

Start by capturing the current session distribution. Compare several short, timestamped observations rather than treating one snapshot as the entire incident. Review authentication and application retry evidence alongside the established sessions.

SELECT SYSDATETIME() AS CapturedLocal,
       login_name, host_name, program_name, status,
       COUNT_BIG(*) AS SessionCount,
       MIN(login_time) AS EarliestVisibleLogin,
       MAX(login_time) AS LatestVisibleLogin
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name, host_name, program_name, status
ORDER BY SessionCount DESC;

Host and program names come from client-provided information. They help associate a workload with an application, but cannot authenticate its origin. Confirm suspicious traffic using connection details and network evidence.

SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE to see all relevant sessions through this DMV. Earlier supported versions use VIEW SERVER STATE. Limited visibility can hide the distribution that matters during an incident.

Fix Logon Storms at the Connection Source

I inspect pool configuration before changing an instance connection limit. I also look for retries that immediately open another connection after failure. Those two checks distinguish ordinary capacity planning from an application repeatedly ringing the doorbell.

Set a bounded pool size appropriate for each application instance. Multiply that size by the actual number of application instances when estimating demand. A sensible individual limit can still produce excessive aggregate connections.

Dispose connections correctly so they return to the pool. Use bounded retry delays and prevent every waiting request from opening another connection simultaneously. A connection failure should not trigger unlimited parallel replacement attempts.

Separate authentication failures from successful connections. Logon triggers execute after authentication succeeds, so they do not stop invalid-password attempts at authentication. Coordinate controls outside the database when that earlier phase is under pressure.

Understand the Instance-Wide Setting

The user connections configuration option caps simultaneous user connections across the instance. Its default zero lets SQL Server manage allocation up to the supported limit. It is not a per-login limit or a connection-rate limiter.

SELECT name, value AS ConfiguredValue,
       value_in_use AS ActiveValue, is_dynamic
FROM sys.configurations
WHERE name IN (N'user connections', N'remote admin connections');

Do not lower this setting casually during an incident. A global cap can block legitimate applications and normal administration along with the noisy source. It also leaves the application retry loop free to continue making attempts.

A planned change needs capacity review and a tested recovery procedure. The documented user connections change requires an engine restart to take effect. Record configured and active values instead of assuming a configuration edit immediately changed behavior. On my test instance, the query above showed is_dynamic as 0 for user connections.

Keep the normal application demand, maintenance workload, and failure scenarios in the same capacity estimate. A limit chosen only from today's average ignores peak traffic. Reserve recovery access through the dedicated administrator connection rather than relying on spare ordinary slots.

Test a Narrow Logon Trigger in Isolation

A logon trigger can reject additional sessions for one selected login. The following example targets only a dedicated test login. It must be tested on an isolated instance after administrator recovery access is verified.

Create LogonTest and LogonObserver beforehand using approved credential management. The observer needs only the session visibility permission appropriate to your SQL Server version. Do not grant broad server permissions to the application login merely to support the trigger.

USE master;
GO
CREATE OR ALTER TRIGGER LimitTestLogons
ON ALL SERVER
WITH EXECUTE AS N'LogonObserver'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() <> N'LogonTest'
        RETURN;
    IF (SELECT COUNT_BIG(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
          AND original_login_name = N'LogonTest') > 3
    BEGIN
        ROLLBACK;
        RETURN;
    END;
END;
GO
DISABLE TRIGGER LimitTestLogons ON ALL SERVER;

The script leaves the example disabled after creation. Keep an existing administrator session open while preparing the test. Enable the named trigger only for the controlled connection test, then disable it again afterward.

ENABLE TRIGGER LimitTestLogons ON ALL SERVER;
-- Perform the controlled test from separate client connections.
-- Run the following from the existing administrator session afterward.
DISABLE TRIGGER LimitTestLogons ON ALL SERVER;

The current logging-in session participates in the count used by this pattern. Test both accepted and rejected connections rather than inferring the boundary from the comparison alone. Simultaneous logins introduce timing concerns, so this is not an exact admission quota.

Keep the trigger short and avoid remote calls or extensive logging inside it. An error in a broadly applied trigger can prevent normal logins, including administrator logins. The example's narrow login condition reduces scope without replacing a recovery plan.

Prepare the Dedicated Administrator Connection

The dedicated administrator connection provides a diagnostic route for sysadmin members. Local access is available by default on supported configurations. Remote access requires its approved configuration and the appropriate network path.

Use the Windows sqlcmd utility from the SQL Server computer for the following local example. Replace the instance name with the tested instance. Windows authentication requires an account that is a SQL Server sysadmin.

REM Command line
sqlcmd -S localhost -E -A -d master

Once connected, disable the exact trigger responsible for blocking normal logins. Execute the following T-SQL inside that administrator connection. Do not disable unrelated server triggers without identifying their role.

DISABLE TRIGGER LimitTestLogons ON ALL SERVER;
SELECT name, is_disabled
FROM sys.server_triggers
WHERE name = N'LimitTestLogons';

Only one dedicated administrator connection is available per instance. Use it for focused diagnosis and release it when finished. Avoid Object Explorer connections or heavy queries that consume the limited recovery route.

Test the route before enabling admission controls. SQL Server Express has additional DAC requirements, and clustered configurations need specific review. An untested emergency command is an optimistic note rather than an operational recovery method.

Contain Logon Storms and Verify Recovery

Network controls can restrict traffic before it reaches authentication and trigger execution. Share timestamps, source details, and the affected listener with the network team. Apply focused containment that preserves approved application and administrator access.

Do not terminate every application session just because one group is large. Identify active requests, open transactions, and business impact first. Session termination can create rollback work and provoke another wave of replacement connections.

Are new connections stabilizing after the application fix, or has the retry loop merely moved elsewhere? Compare login activity and established sessions over a meaningful interval. Verify that legitimate requests and administrator access both recover.

Record the source, corrective change, connection settings, and recovery evidence. Remove the test trigger and observer account through the approved cleanup process when the experiment finishes. Preventing future logon storms depends on connection behavior and tested controls working together.

Related reading on this blog: Adjust Memory with Dedicated Administrator Connection (DAC) and Be Careful with Logon Triggers: Don’t use Host_Name.

A connection limit is not a complete defense, it is one control within an application and recovery design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.