Monitor ExtendedEvents and SQLAudits in SQL Server

Just to add some additional information to this question above about the STIGs from the original author... ultimately, if the tool could remove the audit requirement from SQL Server, this would serve us best:

V-79145 STIG ID SQL6-D0-005500 CAT II states:

"Design and deploy an Audit that captures all auditable events and data items.  In the event a third-party tool is used for auditing, it must contain all the required information including but not limited to events, type, location, subject, date, and time and by whom the change occurred."

https://support.solarwinds.com/Success_Center/Log_Event_Manager_(LEM)/Knowledgebase_Articles/Configure_MSSQL_Auditor_on_a _LEM_Agent

======================================================

V-79223 STIGID: SQL6-D0-010700 CAT II states:

"SQL Server must utilize centralized management of the content captured in audit records generated by all components of SQL Server"

V-79225 STIGID: SQL6-D0-01800 CAT II states:

"SQL Server must provide centralized configuration of the content to be captured in audit records generated by all components of SQL Server."

V-79311 STIGID: SQL6-D0-015900 states:

"The system SQL Server must off-load audit data to a separate log management facility;  this must be continuous and in near real time for systems with a network connection to the storage facility and weekly or more often for stand alone systems."

REGARDING THE "must contain" clause above:

If we can meet these requirements with SolarWinds LEM, it will allow us to close these open vulnerabilities.  The trace file partially meets the requirement.   It would also be beneficial if there could be a listing of the actual trace items captured:

Examples: TraceID 14,15,18,20,102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175,176,177, 178

The above represents the items required in the pre-SQL 2016 STIGs if the SQL methods still entails using traces and had not yet switched over to SQL Audits and Extended Events.   It would be helpful to know exactly what was covered in the default trace given by SolarWinds as it would allow us to re-orient what is collected and how and where to meet this as well as the requirements for specific Audits.  (Roughly 33 of them exist along with the default two from SQL 2016 - system health and telemetry events.)

EXAMPLES: SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SERVER_PRINCIPAL_IMPERSONATION_GROUP, SERVER_OPERATION_GROUP, AUDIT_CHANGE_GROUP (to name a few)

(newbie question) Is there a way to edit the original question without adding a second comment?

As a means of examining the events your system is auditing, the following query is helpful:

SELECT name as 'Audit Name',

status_desc as 'Audit Status'.,

audit_file_path as 'Current Audit File'

FROM sys.dm_server_audit_status

The current list should show roughly 33 if you are meeting the government STIG requirement for SQL 2016:

TRACE_CHANGE_GROUP

DATABASE_PRINCIPAL_CHANGE_GROUP

SERVER_PERMISSION_CHANGE_GROUP

USER_CHANGE_PASSWORD_GROUP

DATABASE_OBJECT_CHANGE_GROUP

SERVER_ROLE_MEMBER_CHANGE_GROUP

DATABASE_OBJECT_ACCESS_GROUP

SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

SERVER_OJBECT_PERMISSION_CHANGE_GROUP

SERVER_PRINICPAL_CHANGE_GROUP

DATABASE_ROLE_MEMBER_CHANGE_GROUP

FAILED_LOGIN_GROUP

DBCC_GROUP

SERVER_STATE_CHANGE_GROUP

DATABASE_PRINCIPAL_IMPERSONATION_GROUP

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

SERVER_PRINCIPAL_IMPERSONATION_GROUP

BACKUP_RESTORE_GROUP

DATABASE_PERMISSION_CHANGE_GROUP

LOGOUT_GROUP

DATABASE_CHANGE_GROUP

AUDIT_CHANGE_GROUP

DATABASE_OWNERSHIP_CHANGE_GROUP

SERVER_OJBECT_CHANGE_GROUP

LOGIN_CHANGE_PASSWORD_GROUP

SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP

SCHEMA_OBJECT_ACCESS_GROUP

SCHEMA_OBJECT_CHANGE_GROUP

SERVER_OPERATION_GROUP

DATABASE_OBJECT_CHANGE_GROUP

DATABSAE_OPERATION_GROUP

If you are picking these up in the monitor (*.sqlaudit), please provide an explanation of how this is done.

Thanks

I'm facing the same problem that you are with STIG requirements on SQL databases. I've mitigated this by creating two separate audits. One includes all required STIG audit groups with the exception of 'SCHEMA_OBJECT_ACCESS_GROUP'; I've limited this to a reasonable log file size and number of files to last until my weekly backup where my logs would then be offloaded to my backup server.

The second audit I run is only for the 'SCHEMA_OBJECT_ACCESS_GROUP'. I've allocated the size and number of files I am able to support. In this way, I do have the audit enabled.. I just don't let it overwhelm my system; I do not retain more than a few hours of logs for this audit. I know its not completely meeting the intent of the STIG... but all the noise generated in that log renders its output nearly useless and in the end it is better to have a functioning server than to execute a DoS on yourself.

If you ever find a way to exclude SQL_Agent processes from being logged or some other useful way of filtering out the garbage generated, please let me know.

Thanks!

I'm facing the same problem that you are with STIG requirements on SQL databases. I've mitigated this by creating two separate audits. One includes all required STIG audit groups with the exception of 'SCHEMA_OBJECT_ACCESS_GROUP'; I've limited this to a reasonable log file size and number of files to last until my weekly backup where my logs would then be offloaded to my backup server.

The second audit I run is only for the 'SCHEMA_OBJECT_ACCESS_GROUP'. I've allocated the size and number of files I am able to support. In this way, I do have the audit enabled.. I just don't let it overwhelm my system; I do not retain more than a few hours of logs for this audit. I know its not completely meeting the intent of the STIG... but all the noise generated in that log renders its output nearly useless and in the end it is better to have a functioning server than to execute a DoS on yourself.

If you ever find a way to exclude SQL_Agent processes from being logged or some other useful way of filtering out the garbage generated, please let me know.

Thanks!