Finding ID | Version | Rule ID | IA Controls | Severity |
---|---|---|---|---|
V-79289 | SQL6-D0-014800 | SV-93995r1_rule | Medium |
Description |
---|
For completeness of forensic analysis, it is necessary to track failed attempts to log on to SQL Server. While positive identification may not be possible in a case of failed authentication, as much information as possible about the incident must be captured. |
STIG | Date |
---|---|
MS SQL Server 2016 Instance Security Technical Implementation Guide | 2018-03-09 |
Check Text ( C-78881r1_chk ) |
---|
Determine if an audit is configured and started by executing the following query. SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status If no records are returned, this is a finding. Execute the following query to verify the "FAILED_LOGIN_GROUP" is included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'FAILED_LOGIN_GROUP' If the "FAILED_LOGIN_GROUP" is not returned in an active audit, this is a finding. |
Fix Text (F-86041r1_fix) |
---|
Add the "FAILED_LOGIN_GROUP" to the server audit specification USE [master]; GO ALTER SERVER AUDIT SPECIFICATION STIG_AUDIT_SERVER_SPECIFICATION WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION STIG_AUDIT_SERVER_SPECIFICATION ADD (FAILED_LOGIN_GROUP); GO ALTER SERVER AUDIT SPECIFICATION STIG_AUDIT_SERVER_SPECIFICATION WITH (STATE = ON); GO |