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. In the Database Management page, click + Add SQL Server Instance. The SQL server instances are automatically discovered and listed out.

3. 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.
4. 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.

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.

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.

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

In EventLog Analyzer UI, go to Settings tab → Database 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 → Applications → SQLServer 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

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 → Applications → SQLServer Audit Logs → Advanced 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

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 → Applications → SQLServer Audit Logs → Advanced Auditing Reports) are populated with this mode of auditing:
   1. Column Modified Reports

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