3 Replies Latest reply on Jul 19, 2019 3:45 PM by ctornello

    Monitor ExtendedEvents and SQLAudits in SQL Server

    crakdkorn

      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?

        • Re: Monitor ExtendedEvents and SQLAudits in SQL Server
          crakdkorn

          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?

            • Re: Monitor ExtendedEvents and SQLAudits in SQL Server
              crakdkorn

              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

                • Re: Monitor ExtendedEvents and SQLAudits in SQL Server
                  ctornello

                  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!