Trace Rollover should be enabled for audit traces that have a maximum trace file size.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2500	DM5267-SQLServer9	SV-23886r2_rule	ECRR-1	Medium

Description
The majority of Microsoft SQL Server security auditing is provided by the trace facility. Traces may be created using system stored procedures or with Microsoft SQL Profiler. The trace must be running in order for security event data to be collected for analysis. Traces can specify a maximum size for the trace file. An action may also be specified when a maximum file size is reached. The trace file rollover option for a defined trace causes the current trace file to close and a new one to be opened with no loss of data. If a maximum file size has been set and the rollover option is not set, the trace stops writing when the maximum file size is reached. If the trace file writes function stops, then auditing is disabled.

Description

The majority of Microsoft SQL Server security auditing is provided by the trace facility. Traces may be created using system stored procedures or with Microsoft SQL Profiler. The trace must be running in order for security event data to be collected for analysis. Traces can specify a maximum size for the trace file. An action may also be specified when a maximum file size is reached. The trace file rollover option for a defined trace causes the current trace file to close and a new one to be opened with no loss of data. If a maximum file size has been set and the rollover option is not set, the trace stops writing when the maximum file size is reached. If the trace file writes function stops, then auditing is disabled.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22720r2_chk )
If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding. Determine the SQL Server Edition: From the query prompt: SELECT CONVERT(INT, SERVERPROPERTY('EngineEdition')) If value returned is 1 (Personal or Desktop Edition) or 4 (Express Edition), if auditing is not enabled or not configured completely to requirements, review the System Security Plan. If this is properly explained in the System Security Plan, this is Not a Finding. If this is not documented or documented poorly in the System Security Plan, this is a Finding. If value returned is 2 (Standard Edition) or 3 (Enterprise/Developer Edition), these findings apply. Determine if trace file rollover is enabled. From the query prompt: SELECT traceid 'TraceID' FROM ::FN_TRACE_GETINFO('0') WHERE property = 1 AND value = 2 If no trace is returned, this is a Finding. If the trace returned for Check DG0145 is not returned above, this is a Finding.

Check Text ( C-22720r2_chk )

If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding.

Determine the SQL Server Edition:

From the query prompt:

SELECT CONVERT(INT, SERVERPROPERTY('EngineEdition'))

If value returned is 1 (Personal or Desktop Edition) or 4 (Express Edition), if auditing is not enabled or not configured completely to requirements, review the System Security Plan. If this is properly explained in the System Security Plan, this is Not a Finding. If this is not documented or documented poorly in the System Security Plan, this is a Finding.

If value returned is 2 (Standard Edition) or 3 (Enterprise/Developer Edition), these findings apply.

Determine if trace file rollover is enabled.

From the query prompt:

SELECT traceid 'TraceID'
FROM ::FN_TRACE_GETINFO('0')
WHERE property = 1
AND value = 2

If no trace is returned, this is a Finding.

If the trace returned for Check DG0145 is not returned above, this is a Finding.

Fix Text (F-19685r1_fix)
Re-create the trace and specify TRACE_FILE_ROLLOVER (option = 2) added to SHUTDOWN_ON_ERROR (option > 4). From the query prompt: EXEC SP_TRACE_CREATE [ @traceid = ] trace_id OUTPUT , [ @options = ] option_value , [ @tracefile = ] 'trace_file' [ , [ @maxfilesize = ] max_file_size ] [ , [ @stoptime = ] 'stop_time' ] [ , [ @filecount = ] 'max_rollover_files' ]