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:
- Navigate to Object Explorer in the MSSQL Server Management Studio.
- In the Object Explorer, expand the Security node.
- Right-click the Audits folder and select the New Audit option. It will open a Create New Audit page.
- Define the Audit Name field with a suitable name for the Audit Object.
- Choose the Application log type in the Audit Destination field.
- Accept the other default settings and save the new audit specifications.
Steps to add a SQL Server
- Navigate to Settings > Log Source Configuration > Database Audit.
- In the Database Management page, click + Add SQL Server Instance. The SQL server instances are automatically discovered and listed out.
- 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.
- 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
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:
- SCHEMA_OBJECT_ACCESS_GROUP
- DATABASE_ROLE_MEMBER_CHANGE_GROUP
- SERVER_ROLE_MEMBER_CHANGE_GROUP
- FAILED_LOGIN_GROUP
- SUCCESSFUL_LOGIN_GROUP
- DATABASE_CHANGE_GROUP
- DATABASE_OBJECT_CHANGE_GROUP
- DATABASE_PRINCIPAL_CHANGE_GROUP
- SCHEMA_OBJECT_CHANGE_GROUP
- SERVER_PRINCIPAL_CHANGE_GROUP
- LOGIN_CHANGE_PASSWORD_GROUP
- SERVER_STATE_CHANGE_GROUP
Note:
- The minimum permission required for a user for advanced auditing is CONTROL SERVER.
- EventLog Analyzer supports DDL/DML auditing for the following editions:
- Prior to Microsoft SQL Server 2012 - Enterprise and Datacenter editions.
- Microsoft SQL Server 2012 and later - Enterprise, Datacenter, and Standard editions.
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
- 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.
- Data types such as text, ntext, and images will not be monitored.
- 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.