SQL Server must allocate audit record storage capacity in accordance with organization-defined audit record storage requirements.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-79227	SQL6-D0-010900	SV-93933r1_rule		Medium

Description
In order to ensure sufficient storage capacity for the audit logs, SQL Server must be able to allocate audit record storage capacity. Although another requirement (SRG-APP-000515-DB-000318) mandates that audit data be off-loaded to a centralized log management system, it remains necessary to provide space on the database server to serve as a buffer against outages and capacity limits of the off-loading mechanism. The task of allocating audit record storage capacity is usually performed during initial installation of SQL Server and is closely associated with the DBA and system administrator roles. The DBA or system administrator will usually coordinate the allocation of physical drive space with the application owner/installer and the application will prompt the installer to provide the capacity information, the physical location of the disk, or both. In determining the capacity requirements, consider such factors as: total number of users; expected number of concurrent users during busy periods; number and type of events being monitored; types and amounts of data being captured; the frequency/speed with which audit records are off-loaded to the central log management system; and any limitations that exist on SQL Server's ability to reuse the space formerly occupied by off-loaded records.

Description

In order to ensure sufficient storage capacity for the audit logs, SQL Server must be able to allocate audit record storage capacity. Although another requirement (SRG-APP-000515-DB-000318) mandates that audit data be off-loaded to a centralized log management system, it remains necessary to provide space on the database server to serve as a buffer against outages and capacity limits of the off-loading mechanism. The task of allocating audit record storage capacity is usually performed during initial installation of SQL Server and is closely associated with the DBA and system administrator roles. The DBA or system administrator will usually coordinate the allocation of physical drive space with the application owner/installer and the application will prompt the installer to provide the capacity information, the physical location of the disk, or both. In determining the capacity requirements, consider such factors as: total number of users; expected number of concurrent users during busy periods; number and type of events being monitored; types and amounts of data being captured; the frequency/speed with which audit records are off-loaded to the central log management system; and any limitations that exist on SQL Server's ability to reuse the space formerly occupied by off-loaded records.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2018-03-09

Details

Check Text ( C-78819r1_chk )
Check the server documentation for the SQL Audit file size configurations. Locate the Audit file path and drive. SELECT max_file_size, max_rollover_files, log_file_path AS "Audit Path" FROM sys.server_file_audits Calculate the space needed as the maximum file size and number of files from the SQL Audit File properties. If the calculated product of the "max_file_size" times the "max_rollover_files" exceeds the size of the storage location or if "max_file_size" or "max_rollover_files" are set to "0" (UNLIMITED), this is a finding.

Check Text ( C-78819r1_chk )

Check the server documentation for the SQL Audit file size configurations. Locate the Audit file path and drive.

SELECT max_file_size, max_rollover_files, log_file_path AS "Audit Path"
FROM sys.server_file_audits

Calculate the space needed as the maximum file size and number of files from the SQL Audit File properties.

If the calculated product of the "max_file_size" times the "max_rollover_files" exceeds the size of the storage location or if "max_file_size" or "max_rollover_files" are set to "0" (UNLIMITED), this is a finding.

Fix Text (F-85979r1_fix)
Review the SQL Audit file location, ensure the destination has enough space available to accommodate the maximum total size of all files that could be written. Configure the maximum number of audit log files that are to be generated, staying within the number of logs the system was sized to support. Update the "max_files" parameter of the audits to ensure the correct number of files is defined.