Detections
Analytics

5.4 Ensure 'SQL Server Audit' is set to capture both 'failed' and 'successful logins' - FAILED_DATABASE_AUTHENTICATION_GROUP

Information

SQL Server Audit is capable of capturing both failed and successful logins and writing them to one of three places: the application event log, the security event log, or the file system. We will use it to capture any login attempt to SQL Server, as well as any attempts to change audit policy. This will also serve to be a second source to record failed login attempts.

Rationale:

By utilizing Audit instead of the traditional setting under the Security tab to capture successful logins, we reduce the noise in the ERRORLOG. This keeps it smaller and easier to read for DBAs who are attempting to troubleshoot issues with the SQL Server. Also, the Audit object can write to the security event log, though this requires operating system configuration. This gives an additional option for where to store login events, especially in conjunction with an SIEM.

Impact:

With the previous recommendation, only failed logins are captured. If the Audit object is not implemented with the appropriate setting, SQL Server will not capture successful logins, which might prove of use for forensics.

Solution

For AWS RDS Instances, please refer to the documentation for configuring SQL Server Audit here: SQL Server Audit
Perform either the GUI or T-SQL method shown:

GUI Method

Expand the SQL Server in Object Explorer.

Expand the Security Folder

Right-click on the Audits folder and choose New Audit...

Specify a name for the Server Audit.

Specify the audit destination details and then click OK to save the Server Audit.

Right-click on Server Audit Specifications and choose New Server Audit Specification...

Name the Server Audit Specification

Select the just created Server Audit in the Audit drop-down selection.

Click the drop-down under Audit Action Type and select AUDIT_CHANGE_GROUP.

Click the new drop-down Audit Action Type and select FAILED_LOGIN_GROUP.

Click the new drop-down under Audit Action Type and select SUCCESSFUL_LOGIN_GROUP.

Click the new drop-down under Audit Action Type and select SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP.

Click the new drop-down under Audit Action Type and select FAILED_DATABASE_AUTHENTICATION_GROUP.

Click OK to save the Server Audit Specification.

Right-click on the new Server Audit Specification and select Enable Server Audit Specification.

Right-click on the new Server Audit and select Enable Server Audit.

T-SQL Method

Execute code similar to:

CREATE SERVER AUDIT TrackLogins
TO APPLICATION_LOG;
GO
CREATE SERVER AUDIT SPECIFICATION TrackAllLogins
FOR SERVER AUDIT TrackLogins
 ADD (FAILED_LOGIN_GROUP),
 ADD (SUCCESSFUL_LOGIN_GROUP),
 ADD (AUDIT_CHANGE_GROUP),
 ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
 ADD (FAILED_DATABASE_AUTHENTICATION_GROUP)
WITH (STATE = ON);
GO
ALTER SERVER AUDIT TrackLogins
WITH (STATE = ON);
GO

Note: If the write destination for the Audit object is to be the security event log, see the Books Online topic Write SQL Server Audit Events to the Security Log and follow the appropriate steps.

Default Value:

By default, there are no audit object tracking login events.

See Also

https://workbench.cisecurity.org/benchmarks/11963

Item Details

Category: AUDIT AND ACCOUNTABILITY

References: 800-53|AU-3, 800-53|AU-3(1), 800-53|AU-7, 800-53|AU-12, CSCv7|4.9

Plugin: MS_SQLDB

Control ID: 2d3e74199ae4de695c7e8ba3280ad3dda752f446c6ae98074e3446cb4dbfc9f5