The Login Auditing Trap: Why Extended Events is the Only Production-Ready Path

The Problem with Default Auditing

For years, the standard approach for auditing successful logins in SQL Server has been toggling that little checkbox in the Server Properties under Security. It feels easy, it feels native, and it is a performance nightmare waiting to happen. When you enable 'Both failed and successful logins' auditing at the server level, you aren't just logging security data; you are weaponizing your SQL Server Error Log against yourself.

In a high-transaction environment with thousands of connections per minute—think microservices or poorly coded applications that don't use connection pooling—your error log will swell to gigabytes in hours. I’ve seen production instances where the error log became so bloated that it took minutes just to open the file during a critical outage. If you can't read your logs when the server is crashing because they are filled with 'Login succeeded for user...', you've failed at basic administration.

The Cost of Traditional Audit Objects

SQL Server Audit objects, introduced in 2008, were supposed to be the answer. While they are more flexible than the basic error log toggle, they still carry overhead. Because the Audit object writes to a file or the Windows Security Log, it introduces a synchronous or asynchronous write delay. In highly concurrent environments, the 'SUCCESSFUL_LOGIN_GROUP' can become a bottleneck. Furthermore, the management of those audit files adds another layer of storage complexity that most DBAs don't want to babysit.

We need a way to capture who is connecting, where they are coming from, and what application they are using, without the disk I/O penalty or the log pollution. That is where Extended Events (XEvents) comes in.

Why Extended Events Wins

Extended Events is the lightweight, high-performance tracing system that replaced the deprecated SQL Trace years ago. For login auditing, it is vastly superior because it allows for granular filtering at the engine level before the data is ever written to memory or disk.

With XEvents, I can target the login event and specifically exclude known service accounts that connect every second. By filtering out the noise of the 'known good' traffic, we can focus the audit on the specific actors or connection types that actually matter for security compliance. This reduces the event frequency from thousands per second to a manageable trickle.

Implementation Strategy

When setting up a login audit via Extended Events, the key is the sqlserver.login event. This event fires whenever a connection is established. To make this production-ready, you should capture at least the following fields:

1. session_id: To correlate with other system views.

2. client_hostname: To identify the source machine.

3. nt_username: To see the Windows identity.

4. options_text: To see the connection strings and settings.

Crucially, you must use a ring_buffer target for real-time troubleshooting or an event_file target for long-term compliance. The ring_buffer is excellent because it resides in memory and automatically cycles, meaning it will never crash your server by filling up a disk. For formal auditing requirements, the event_file target is necessary, but you should always set a reasonable max file size and file rollover count.

Filtering Out the Noise

The most important step in an XEvents login audit is the predicate (the WHERE clause). If you have a monitoring tool like SentryOne, SolarWinds, or Redgate, those tools are hitting your server every few seconds. You do not need to audit them. Use the client_app_name or nt_username filters to exclude these known entities. By ignoring the 'chatter,' you ensure that when an unauthorized user or an unexpected IP address hits the server, the data is there and easy to find, rather than buried under 10 million rows of monitoring heartbeat logs.

Performance Considerations

Even XEvents isn't free. While it is significantly lighter than SQL Trace or Error Log auditing, you should still follow the principle of least privilege regarding data collection. Don't capture the collect_database_name or other expensive actions unless your audit specifically requires it. In my experience, the overhead of a well-filtered XEvent session on a modern server (16+ cores) is effectively immeasurable—usually under 1% of total CPU utilization even under heavy load.

Summary

Stop using the Server Properties checkbox for login auditing. It is a legacy feature that belongs in the SQL 2000 era. If you need to know who is accessing your data, Extended Events provides the most robust, performant, and filterable mechanism available. It protects your Error Log for what it was meant for—actual errors—and ensures that your security auditing doesn't become the very thing that takes your production environment offline.