SQL Server – Reducing MSSQL Audit Size Growing Out of Control

auditsql server

I am trying to setup audits for a Veeam backup server.

I used the audit specifications from STIG – https://www.stigviewer.com/stig/ms_sql_server_2016_instance/

I haven't setup any database auditing at this point, just the server from above link.

The audit is writing 1gb every 4 minutes. Over 100gb every day!

Is there any way to get the size down? Maybe removing Select statements from audits? If so, how can I do that. Please point to a tutorial or something – I've searched for days …

TIA

Best Answer

Auditing should be enabled based on requirement from Compliance/IT security or Audit department of your organization. If you enable auditing as mentioned in this link, it would start auditing each and every thing. It will not only make audit files to grow rather, it would impact on performance of your instance badly.

Ideally if you follow C2 auditing, it captures basics of auditing and is accepted internationally.

C2 Auditing and Common Criteria Compliance are two internationally accepted auditing standards.

C2 audit mode is the auditing option that can be used to facilitate auditing standard. By enabling C2 auditing, it allows the administrator to enable a comprehensive type of auditing, logging. This is named as C2 audit mode because it is logging of the form specified by the U.S. Department of Defense regulations to meet the certification at the C2 level of trust. Those regulations were specified in a document called the Trusted Computer System Evaluation Criteria, often referred to as the “Orange Book” in the “Rainbow Series.” The Orange Book is now depreciated in favor of the Common Criteria. Nonetheless, C2 Auditing is still a commonly used term.

C2 audit mode generally means assigning a unique generated audit ID to each group of related processes, starting at login. Thereafter, certain forms of system calls performed by every process are logged with the audit ID.

You might read more about C2 auditing at this link.

There is another method of auditing accepted called "Common Criteria Compliance" and details are as below:

C2 auditing has been deprecated by the Common Criteria specification which was developed by the European Union. Common Criteria Compliance is internationally recognized set of guidelines for security for information technology products. This is applied to operating systems, Databases, Network Devices and Smart cards etc. Common criteria certified products have been rigorously evaluated by accredited third party security labs.

This option is available same versions as C2 Audit but Common Criteria Compliance is available in Enterprise and Data Center editions of SQL Server 2008 R2 and after versions. However, with respect to the SQL Server, if you are complying with either C2 or common criteria, the audit result is similar. In the case of Common Criteria Compliance, audited data can be viewed from system views which is more secure than the C2 audit mode.

if you enable one of them, your problem of file size and performance should go.

In case, you are really interested to get to the root of your culprit in your current scenario then, you should query your audit log and group them by action and disable that event, same can be done using below query:

SELECT action_id, count(*)
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT)
group by action_id

Please remove SCHEMA_OBJECT_ACCESS_GROUP from auditing events and you should be fine for disabling select statement from audit. You can check more about this at link.

Hope this helps.

Related Question