SQL Server 2016 Audit Trail Preservation to Comply with V-79147, V-79149, and V-79227

The SQL 2016 STIGs addressing audit trail preservation contain some confusing language, apparent contradictions, omissions, and at least one glaring error.  The purpose of this article is to make sense of DISA’s intent—thereby resolving any apparent contradictions—and to call attention to the remaining omission and error.

A casual reading of vulnerabilities V-79147, V-79149, and V-79227 might lead the reader to any number of possible conclusions.  This is primarily because the applicability tests are not as fully described as they could be, and were completely omitted from V-79227.

Worse, a technical error in the “Check Text” section of the STIG for both V-79149 and V-79227 allows a path to a false passing condition that I suspect is widely used to close those two vulnerabilities without actually meeting DISA’s original intent.

First, let’s address applicability, as this will eliminate the contradiction that would otherwise become apparent after we fix the error.

Audit trail preservation is addressed in V-79147 and V-79149 and the question of which takes precedence (i.e. “availability” or “audit trail completeness”) defines which of these two STIGs is applicable.

Both STIGs state that systems where audit trail completeness is paramount will most likely be at a lower MAC level than MAC I; and that the final determination is the prerogative of the application owner, subject to Authorizing Official concurrence.  

From this we can ascertain that most MAC II and MAC III systems will prioritize “audit trail completeness” and thus be governed by V-79147.  MAC I systems, on the other hand, may prioritize “availability” and, if so, be governed by V-79149. 


V-79147 – Applicable when “audit trail completeness” takes precedence

  • The objective is a complete audit trail of all audited administrative activity, even at the cost of application availability.
  • The STIG explicitly states that the audit MUST be configured to shutdown upon audit failure.
  • The STIG implies in the Fix Text where it adds, “…to include running out of space for audit logs” that overwriting files should NOT be allowed, though it fails to check for this, or recommend a fix.
  • Because overwriting audit files clearly favors “availability” over “audit trail completeness”, the policy should be to prohibit the overwriting of audit files when “audit trail completeness” takes precedence, even though the STIG does not explicitly require this.

V-79149 – Applicable when “availability” takes precedence

  • The objective is to prioritize application availability, even at the cost of audit trail completeness.
  • The STIG explicitly states that the audit MUST be configured to overwrite audit files if necessary to prevent audit failure.
  • The STIG implies that audit failure should not result in a shutdown, though it fails to check for this, or recommend a fix for it.
  • Since shutdown on audit failure clearly favors “audit trail completeness” over “availability”, the policy here should be to prohibit shutdown on audit failure when “availability” takes precedence, even though the STIG does not explicitly require this.
  • Please note that the requirement for shutdown on audit failure exists only in V-79147, which is not-applicable when “availability” takes precedence.

If that didn’t seem confusing enough, just wait.  We have an additional problem;

The max_rollover_files error.

The DISA “Check Text” for these two vulnerabilities contains the following similar errors:     


“If the "storage_type" is "FILE" and "max_rollover_files" is greater than zero, this is not a finding. Otherwise, this is a finding.”


“If the [max_rollover_files] is greater than zero, this is not a finding.” 

The [max_rollover_files] setting is never zero.   When STIG audit guidelines switched from the recommended trace files (dating back to the 2005/2008 STIGs) to the new SQL Audit features, the max_rollover_files flag functionality was changed, and this change was missed when the audit STIGs were updated.

The condition DISA was trying to avoid (unlimited rollover, which never overwrites files) was indicated by a Zero in the pre-SQL Audit days when trace files were used, but today is indicated by a value of 2147483647.  Accordingly, it is this value we should check for, to accurately accomplish the original intent of the STIG.

In either of these cases, the configuration of the audit, the off-loading of files to a centralized management location, and the space provided for these files MUST all still minimize the chances of running out of audit space.

One final note on audit trail completeness.  When we correct the Zero error in V-79227, it becomes clear the STIG mandates audits should be configured to allow the overwriting of audit files, but this creates a possible conflict with V-79147, which mandates the opposite.

So what’s going on?

V-79227 is similar to V-79149, in that it mandates audits be configured to allow overwriting of audit files, but it fails to ask the same important question, whether “availability” or “audit trail completeness” take precedence.  The omission of this question is an obvious oversight, and the policy should be to consider V-79227 as governed by the same applicability test as V-79149.

Now, V-79147 is the applicable STIG when “audit trail completeness” takes precedence, and V-79149 & V-79227 are both applicable (and compatible) when “availability” takes precedence.

That should be clear as mud, right?


Leave a comment

Please note, comments must be approved before they are published