There is a good discussion on how to use an old deprecated approach to monitoring SQL Server (audits) that uses a trace (SQLAuditor.exe) but the trace misses much of the information required by the STIGs. Meanwhile, with the STIG, we generate a great deal of information (I've seen it reach 20 GIG a day but we found a way to reduce that) and we are looking for a tool that will help us monitor the massive amount of audit files (*.sqlaudit and *.xel) that SQL Server 2016 generates. The trace is cool - I like it, but unfortunately, it doesn't meet the requirement of the STIGs for SQL 2016.
What am I missing?
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."
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'
The current list should show roughly 33 if you are meeting the government STIG requirement for SQL 2016:
If you are picking these up in the monitor (*.sqlaudit), please provide an explanation of how this is done.
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.