SQL Server must shutdown immediately in the event of an audit failure, unless an alternative audit capability exists.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41022	SQL2-00-012800	SV-53397r1_rule		Medium

Description
It is critical that, when SQL Server is at risk of failing to process audit logs as required, it takes action to mitigate the failure. If the system were to continue processing without auditing enabled, actions can be taken on the system that cannot be tracked and recorded for later forensic analysis. In many system configurations, the disk space allocated to the auditing system is separate from the disks allocated for the operating system; therefore, this may not result in a system outage. This forces the application to detect and take actions. A failure of SQL Server auditing will result in either the database continuing to function without auditing, or halt of SQL Server operations. In this case, the database must cease processing immediately in order to not allow unlogged transaction to occur.

Description

It is critical that, when SQL Server is at risk of failing to process audit logs as required, it takes action to mitigate the failure. If the system were to continue processing without auditing enabled, actions can be taken on the system that cannot be tracked and recorded for later forensic analysis. In many system configurations, the disk space allocated to the auditing system is separate from the disks allocated for the operating system; therefore, this may not result in a system outage. This forces the application to detect and take actions. A failure of SQL Server auditing will result in either the database continuing to function without auditing, or halt of SQL Server operations. In this case, the database must cease processing immediately in order to not allow unlogged transaction to occur.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-05

Details

Check Text ( C-47639r2_chk )
From the query prompt: SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0') All currently defined traces for the SQL Server instance will be listed. If no traces are returned, this is a finding. Determine the trace being used for the auditing requirement. Replace # with a traceid being used for the auditing requirements. From the query prompt: SELECT CAST(value AS INT) FROM ::FN_TRACE_GETINFO('#') where property = 1 and (cast(value as int) & 4 = 4) If the traces are not set to 4, this is a finding.

Check Text ( C-47639r2_chk )

From the query prompt:

SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

All currently defined traces for the SQL Server instance will be listed. If no traces are returned, this is a finding.

Determine the trace being used for the auditing requirement. Replace # with a traceid being used for the auditing requirements.

From the query prompt:
SELECT CAST(value AS INT) FROM ::FN_TRACE_GETINFO('#')
where property = 1 and (cast(value as int) & 4 = 4)

If the traces are not set to 4, this is a finding.

Fix Text (F-46321r2_fix)
Modify the SQL Server audit setting to immediately shutdown the database in the event of an audit failure by setting property 1 to a value of 4 for the audit.