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
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
- 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:
- 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
- The Application type events collected for the corresponding Windows device are used for this mode of auditing.
- The following report groups (Reports tab → Applications → SQLServer Audit Logs) are populated with this mode of auditing:
- SQL Server Events
- SQLServer Trend Report
- DDL Auditing Report
- DML Auditing Report
- Auditing Account Management
- Auditing Server Report
- Attack Reports
- 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
- 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.
- Following reports (Reports tab → Applications → SQLServer Audit Logs → Advanced Auditing Reports) are populated with this mode of auditing:
- Last Login Time Report
- Delete Operations Report
- Logins Information Report
- Most Used Tables
- Table Update Report
- Index Information Report
- Server Information Report
- Waits Information Report
- Blocked Processes Report
- Schema Change History
- Object Change History
- Connected Applications Report
- Security Changes Report
- Permissions Information Report
- Last Backup of Database
- Last DBCC Activity
Note: The queries to fetch logs will succeed only if Advanced Auditing is enabled.
Case 3: Column Integrity Monitoring
- 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).
- 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.
- Advanced Auditing needs to be enabled for the configuration to succeed.
- Following reports (Reports tab → Applications → SQLServer Audit Logs → Advanced Auditing Reports) are populated with this mode of auditing:
- Column Modified Reports
Note: Although Advanced Auditing needs to be enabled for the creation of the trigger, it can be disabled later. The required logs will be fetched even if Advanced Auditing has been disabled.
Case 4: Events Collected
The following are the IDs of events that are collected when advanced auditing is enabled:
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).