Why are SQL Server audit logs not collected?

Why are SQL Server audit logs not collected?

Case 1: Is Advanced Auditing enabled?  

  1. Open EventLog Analyzer and go to Settings > Database Audit > SQL Servers.

  2. The DDL/DML Monitoring column should show Manage for the required instance. If it says Not configured, then edit the required instance, and enable Advanced Auditing.

 

Note:

  1. Advanced Auditing always needs to be enabled for successful database auditing.

  2. Advanced Auditing needs to be enabled only for the initial configuration of the DDL/DML Monitoring and Column Integrity Monitoring features.

 

 

Case 2: Are the audit policies configured?  

  1. Open SQL Server Management Studio in the Windows machine in which SQL Server is installed, and connect to the required instance.

  2. Click the Security option.

  3. The Server Audit Specifications section should have an audit specification with a name starting with ME_LOG360. Ensure it is enabled.

  4. Double-click the audit specification:
          a. Find the name of the mapped audit in the Audit field. An audit with the same name should be present under the Audits section (Security > Audits). Ensure it is enabled.
          b. The Audit Action Type should contain the following policies:

                    

        1. SCHEMA_OBJECT_ACCESS_GROUP

        2. DATABASE_CHANGE_GROUP

        3. SCHEMA_OBJECT_CHANGE_GROUP

        4. DATABASE_OBJECT_CHANGE_GROUP

        5. DATABASE_OBJECT_ACCESS_GROUP

        6. DATABASE_PRINCIPAL_CHANGE_GROUP

        7. SERVER_PRINCIPAL_CHANGE_GROUP

        8. SERVER_OBJECT_CHANGE_GROUP

        9. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

        10. LOGIN_CHANGE_PASSWORD_GROUP

        11. USER_CHANGE_PASSWORD_GROUP

        12. BACKUP_RESTORE_GROUP

        13. SERVER_ROLE_MEMBER_CHANGE_GROUP

        14. DATABASE_ROLE_MEMBER_CHANGE_GROUP

        15. DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

        16. DATABASE_PERMISSION_CHANGE_GROUP

        17. SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

        18. SERVER_OBJECT_PERMISSION_CHANGE_GROUP

        19. SERVER_PERMISSION_CHANGE_GROUP

        20. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

        21. DATABASE_OWNERSHIP_CHANGE_GROUP

        22. SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

        23. SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP

        24. AUDIT_CHANGE_GROUP

        25. TRACE_CHANGE_GROUP

        26. SERVER_STATE_CHANGE_GROUP

        27. SUCCESSFUL_LOGIN_GROUP

        28. FAILED_LOGIN_GROUP

        29. LOGOUT_GROUP

    Case 3: Are EVENTID=33205 events logged in the Event Viewer?  

    1. In the target Windows machine where SQL Server is installed, open the Event Viewer > Application, and check for events with EVENTID=33205.

    2. If events with EVENTID=33205 are not present, simulate some events, e.g.: log in to the SQL Server instance using SQL Server Management Studio. A successful login will generate an event with EVENTID=33205 and action_id=LGIS, and a failed login will generate an event with EVENTID=33205 and action_id=LGIF.

              a. If events still don't appear check Case 4: Is the Event Viewer holding events for more than the monitoring interval (default: 10 minutes)?

     

    Case 4: Is the Event Viewer holding events for more than the monitoring interval (default: 10 minutes)?  

    Are events in the Event Viewer getting overwritten with new events before EventLog Analyzer can collect them? Ensure that the time difference between the first and the last log of Event Viewer is greater than the monitor interval. If not, increase the size of the Security category.

     

    Case 5: Are any Log Collection Filter profiles in EventLog Analyzer filtering out the logs?  

    In EventLog Analyzer, go to Settings > Admin Settings > Log Collection Filters, and ensure that no filter profiles are configured to drop the expected logs.



                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • What to do if the MSSQL logs are not being collected?

                      Open the EventLog Analyzer UI, go to the Settings tab ⇾ Configuration ⇾ Manage Application Sources ⇾ SQL Servers tab ⇾ click on "Update" next to the Instance Name ⇾ check the Server details and verify the Instance Authentication. Only if the ...
                    • How to collect the users Enabled/Disabled SQL logs?

                      The Enable/Disable logs will be recorded in the Event Viewer in the following scenarios: In SQL Server Management Studio, Security ⇾ Logins ⇾ Right-click on any user ⇾ Properties ⇾ Status ⇾ Login section ⇾ select Disabled/Enabled. By executing the ...
                    • Why are some SQL Server reports showing no data?

                      Case 1: Are the required audit policies configured? Open SQL Server Management Studio application in the Windows machine in which SQL Server is installed, and connect to the required instance. Click the Security option. The Server Audit ...
                    • What are the audit policies required to generate events for an SQL Server report?

                      Existing Reports vs SQL Server Policies S. no. Report Group Total Reports Report Name Criteria Required Server-level Audit Action Types 1 SQL Server Events 2 All Events - - Important Events - - 2 SQLServer Trend Report 2 Read Event Trend ...
                    • How do I fix the issue of being unable to configure the SQL Server application?

                      To open the SQL Server Configuration Manager to view the configurations of an SQL Server instance: In the machine where SQL Server is running, connect to the Microsoft Management Console via Run > mmc. In the Microsoft Management Console, go to File ...