Adding SQL server

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.
• Click Add.

Advanced Auditing

The following are configured when Advanced Auditing is enabled.

DDL/DML monitoring A Server Audit is created with a Server Audit Specification 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

Database auditing

Only enabled SQL Server instances will be audited. Data presented in the reports is retrieved and updated at the last hour of each day.

Column Integrity Monitoring

1. 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.
2. Data types such as text, ntext, and images will not be monitored.
3. Columns to be monitored must be chosen carefully, as triggers are used to monitor changes and is a performance intensive operation.

Events Collected

The following are the IDs of events that are collected when advanced auditing is enabled:

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

SQL Server Host Activity Reports - 18100

SQL Server Integrity Reports - 806, 825

SQL Servers Logins Reports - 18453, 18454, 18455, 28046, 15537, 15538, 18401, 18451, 18456, 18461, 18462, 18463, 18464, 18465, 18466, 18467, 18468, 18470, 18471, 18486, 18487, 18488, 28048

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

SQL Server Violation Reports - 17308, 17311

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.