Failed login auditing is switched off

What this check looks for

Two separate settings, reported under one finding because they are the same loss.

Login auditing. Read from the registry with xp_instance_regread, at Software\Microsoft\MSSQLServer\MSSQLServer\AuditLevel:

Value Meaning Reported
0 None Yes
1 Successful logins only Yes
2 Failed logins No
3 Both No

Values 0 and 1 both mean failed logins are not recorded. The registry read requires sysadmin and is wrapped so that Linux, Amazon RDS and a locked down registry produce no finding rather than an error.

The default trace. sys.configurations where name is default trace enabled and value_in_use is 0.

Why it matters

A password guessing attack against this instance would leave no trace anywhere.

That is the whole of it. There would be no record of who tried to connect, from where, with which login name, or how many times. Not during the attack, and not afterwards when somebody asks what happened.

The installed default is to record failed logins, and it costs nothing to leave on. It gets switched off for one reason: somebody found the error log noisy. That is usually a legitimate complaint, because a misconfigured application retrying a bad password writes a line every few seconds and buries everything else. The right answer is to fix the application, and the fast answer is to turn off the audit, and the fast answer is the one that gets taken.

Two knock-on effects are worth knowing:

  • The error log stops being usable as evidence. After a security incident, the failed login record is the first thing anyone asks for.
  • Other checks in this report depend on it having been recorded. Several of the error log checks can only find what was written down.

The default trace is the second half. It keeps a rolling record of configuration and security changes on the instance: logins created, roles granted, settings altered, files added. When it is off, that history does not exist either, and it is the only such record on an instance with no dedicated audit configured.

How to confirm it yourself

DECLARE @auditLevel INT;

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'AuditLevel',
     @auditLevel OUTPUT;

SELECT @auditLevel AS [audit_level],
       CASE @auditLevel WHEN 0 THEN 'None'
                        WHEN 1 THEN 'Successful logins only'
                        WHEN 2 THEN 'Failed logins'
                        WHEN 3 THEN 'Both'
                        ELSE 'Unknown' END AS [meaning];

SELECT [name], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'default trace enabled';

If the default trace is on, this is what it has been keeping:

SELECT TOP (50) *
  FROM sys.fn_trace_gettable(
         CONVERT(NVARCHAR(500),
           (SELECT [value] FROM sys.fn_trace_getinfo(1) WHERE [property] = 2)), DEFAULT)
 ORDER BY [StartTime] DESC;

How to fix it

Set login auditing to failed logins at minimum. In SQL Server Management Studio, right click the instance, Properties, Security, and choose Failed logins only or Both failed and successful logins. The change requires a service restart, which is the reason it often waits for a maintenance window.

The equivalent without the dialog:

EXEC xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'AuditLevel',
     REG_DWORD,
     2;   -- 2 is failed logins, 3 is both

Turn the default trace back on, which takes effect immediately with no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'default trace enabled', 1;
RECONFIGURE;

Then deal with the noise that caused this. If the error log fills with failed logins from one application:

  • Find the source. The failed login entry names the login and the client host.
  • Fix the credential, rather than silencing the record of it failing.
  • Cycle the error log more often and keep more files, so a noisy period does not push out the history you care about.

For anything with a compliance requirement, a SQL Server Audit specification is the proper instrument. Login auditing and the default trace are the free floor, not the ceiling.

How long it takes

About an hour and a half, most of which is arranging the service restart. The default trace is a two minute fix.

Report Why you would go there
Security Posture The instance’s security surface as a whole.
Logins Every login, and what it can do.
Error Log What is being recorded now, and what the noise was.
master Server Audits Whether a proper audit specification already exists.
What Changed Configuration changes the default trace captured.
Structure Change Log Schema changes from the same source.
Check
SQL logins with blank passwords or policy turned off The accounts an attacker would try, which this would have recorded.
Database connections as sa A related sign that authentication is not taken seriously here.
Large error log files Often the reason auditing was switched off.
Error log not recycled The other half of the error log noise problem.

Frequently asked questions

Why is “successful logins only” reported as a problem? Because it records the logins that worked and not the ones that did not, which is exactly backwards for detecting an attack. It is also the noisier of the two on a busy instance.

Does turning this on really need a restart? Yes, for the audit level. The default trace does not.

Will this fill my error log? It writes a line per failed login. On a healthy instance that is a handful a week. If it is more than that, the volume itself is the finding.

Nothing is reported on our Linux instance. The registry read does not apply there and is caught rather than failing the scan. Check the audit level through the instance properties instead.

Is the default trace deprecated? It has been marked as such for several versions and is still present and still on by default. Until it is actually removed, an instance with it off has less history than one with it on, for no benefit.