lhs-panel Click here to expand

Adding SQL server

EventLogAnalyzer supports the following SQL auditings:

  • DDL/DML Monitoring
  • Advanced Auditing
  • Column Integrity Monitoring
  • Supported SQL Server Versions:

  • SQL Server 2012 and above : Full support
  • SQL Server 2008 :
    • Enterprise and Datacenter editions : Full support
    • Other editions : Partial Support(Only Advanced auditing and Column Integrity Monitoring available)

    Steps to create a SQL Server Audit Object manually

    Carry out the following steps to create a SQL Server Audit Object manually:

    1. Navigate to Object Explorer in the MSSQL Server Management Studio.
    2. In the Object Explorer, expand the Security node.
    3. Right-click the Audits folder and select the New Audit option. It will open a Create New Audit page.
    4. Define the Audit Name field with a suitable name for the Audit Object.
    5. Choose the Application log type in the Audit Destination field.
    6. Accept the other default settings and save the new audit specifications.

    Steps to add a SQL Server

    1. Navigate to Settings > Log Source Configuration > Database Audit.
    2. sql-server-addition
    3. In the Database Management page, click + Add SQL Server Instance. The SQL server instances are automatically discovered and listed out.
    4. sql-server-autodiscovery
    5. Select the SQL Server instance(s) you wish to monitor and click Next. You will be taken to the Credential Configuration page and prompted to enter valid credentials.
    6. If you wish to use the default credentials, select the check-box (default credentials could be the device or domain or logged on credentials). Alternatively, you can enter a username and password in the credentials field and click Save.
    sql-server-credential-configuration

    If the SQL Server instance you wish to add for monitoring is not discovered automatically, click

    + Add Manually and you will be prompted to enter details for Windows Server configuration and SQL Server instance configuration.

    Steps to add a SQL Server instance manually

    Windows server configuration

    • Select the Windows server and enter valid credentials. Alternatively, you can use the default credentials.
    • SQL Server instance configuration
    • Enter the instance name, port number, and credentials in the given fields
    • Enable or disable Advanced Auditing.
    • Note: Enabling advanced auditing will create an audit policy and disabling advanced auditing will remove the audit policy on the selected SQL Server instance.

    • Select the instance authentication method (Windows or SQL authentication) from the available dropdown menu.
    • Note: Windows Authentication is recommended for Advanced Auditing.

    • Click Add.
    sql-server-manual-configuration

    Viewing added SQL Server instances

    EventLog Analyzer lists all the SQL Server instances being monitored. From this list, you can enable, disable, or delete SQL Server instances.

    Viewing all added SQL Servers

    What are the different types of SQL auditing performed by EventLog Analyzer?

    In EventLog Analyzer UI, go to Settings tabDatabase Audit page → SQLServer Audit Logs to view the status of each mode.

    Case 1: DDL/DML Monitoring

    1. When Advanced Auditing is enabled for an instance in EventLog Analyzer, a server-level audit specification is created in the SQL Server instance for the following audit action types:
      1. SCHEMA_OBJECT_ACCESS_GROUP
      2. DATABASE_ROLE_MEMBER_CHANGE_GROUP
      3. SERVER_ROLE_MEMBER_CHANGE_GROUP
      4. FAILED_LOGIN_GROUP
      5. SUCCESSFUL_LOGIN_GROUP
      6. DATABASE_CHANGE_GROUP
      7. DATABASE_OBJECT_CHANGE_GROUP
      8. DATABASE_PRINCIPAL_CHANGE_GROUP
      9. SCHEMA_OBJECT_CHANGE_GROUP
      10. SERVER_PRINCIPAL_CHANGE_GROUP
      11. LOGIN_CHANGE_PASSWORD_GROUP
      12. SERVER_STATE_CHANGE_GROUP
    2. The Application type events collected for the corresponding Windows device are used for this mode of auditing.
    3. The following report groups (Reports tab → ApplicationsSQLServer Audit Logs) are populated with this mode of auditing:
      1. SQL Server Events
      2. SQLServer Trend Report
      3. DDL Auditing Report
      4. DML Auditing Report
      5. Auditing Account Management
      6. Auditing Server Report
      7. Attack Reports
      8. Additional Security Reports

    Note: Advanced Auditing needs to be enabled for server-level audit specification to be created. It can be disabled later. The required logs will be fetched even if Advanced Auditing has been disabled.

    Case 2: Database Auditing

    1. When Advanced Auditing is enabled for an instance in EventLog Analyzer, queries are executed every night at 11PM to collect events in this auditing mode.
    2. Following reports (Reports tab → ApplicationsSQLServer Audit LogsAdvanced Auditing Reports) are populated with this mode of auditing:
      1. Last Login Time Report
      2. Delete Operations Report
      3. Logins Information Report
      4. Most Used Tables
      5. Table Update Report
      6. Index Information Report
      7. Server Information Report
      8. Waits Information Report
      9. Blocked Processes Report
      10. Schema Change History
      11. Object Change History
      12. Connected Applications Report
      13. Security Changes Report
      14. Permissions Information Report
      15. Last Backup of Database
      16. Last DBCC Activity
    Note: The queries to fetch logs will succeed only if Advanced Auditing is enabled.

    Case 3: Column Integrity Monitoring

    1. When Column Integrity Monitoring is configured, EventLog Analyzer creates a trigger in the SQL Server instance which automatically writes an event in Event viewer when the monitored column of the given table is modified (i.e. an UPDATE query is executed).
    2. The Column Integrity Monitoring report provides information on the changes in a monitored column including who changed the value, at what time the value was changed, and the database table in which the value was changed. Additionally, the old and new values are shown.
    3. Data types such as text, ntext, and images will not be monitored.
    4. Columns to be monitored must be chosen carefully, as triggers are used to monitor changes and is a performance intensive operation.
    5. Following reports (Reports tab → ApplicationsSQLServer Audit LogsAdvanced Auditing Reports) are populated with this mode of auditing:
      1. Column Modified Reports
    Note: To enable Column monitoring, the following prerequisite to be met
    1. Advanced Auditing should be enabled to create Trigger in the SQL server. it can be disabled later, once the trigger is created.
    2. The trigger that has to be created is of type "AFTER TRIGGER" , hence a primary key must be present in the table for the trigger to be fired.

    Case 4: Events Collected

    When advanced auditing is enabled, the following event id's will be enabled in the SQL server for the following reports.

    DBCC Information Reports - 211, 427, 610, 8440, 9100, 15612, 15615, 2509, 2510, 2514, 17557

    Host Activity Reports - 18100

    Integrity Reports - 806, 825

    Permission Denied Reports - 229, 300, 230, 262, 916, 5011

    Violation Reports - 17308, 17311

     

    Note: The minimum permission required for SQL server auditing is given in this link (under SQL server auditing section).

    Copyright © 2020, ZOHO Corp. All Rights Reserved.

    Get download link