Chat now with support
Chat with Support

Change Auditor for SQL Server 7.1.1 - User Guide

SQL Server Auditing

•
Introduction
•
SQL Auditing page
•
SQL Auditing templates
•
SQL Auditing wizard
•
SQL Server event logging

Introduction

The SQL Auditing page on the Administration Tasks tab displays details about each SQL Auditing template created and allows you to add new auditing templates or modify and delete templates. It will initially contain an entry for the Best Practice SQL Auditing Template that ships with Change Auditor.

* 
NOTE: Due to a Microsoft hotfix, Change Auditor agents will not capture SQL-related events until the following action is taken on each SQL Server to be monitored:
1
Select Start | All Programs | Microsoft SQL Server | Configuration Tools | SQL Server Configuration Manager.
2
Select the SQL Server Services node.
3
Right-click SQL Server (MSSQLSERVER) and select Properties.
4
Select the Startup Parameters tab.
5
Enter -T1906 and click Add.
6
Restart the SQL Server (MSSQLSERVER) service.
Repeat steps 3 through 6 for each instance on the server (SQL Server (instance name)).
NOTE: Launching SQL Server Configuration Manager with UAC enabled can cause issues with SQL server properly writing to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\etwcnf.xml after the trace flag is enabled.
Once you add the - T1906 flag, ensure the etwcnf.xml exists and is not empty.
NOTE: The folder path changes based on the version of SQL.

SQL Auditing page

The SQL Auditing page is displayed when SQL Server is selected from the Auditing task list in the navigation pane of the Administration Tasks tab. From this page you can start the SQL Auditing wizard to specify the SQL instances and the operations to audit. You can also edit existing templates and remove templates that are no longer being used.

 
* 
NOTE: Authorization to use the administration tasks on the Administrations Tasks tab is defined using the Application User Interface page under the Configuration task list. If you are denied access to the tasks on this page, refer to the Change Auditor User Guide for more information on how to gain access.

The SQL Auditing page contains an expandable view of all the SQL Auditing templates that have been defined. Initially, only the Best Practice SQL Auditing template will be listed on this page. To add a new template to this list, click the Add tool bar button. Once added, the following information is provided for each template:

Template
Displays the name assigned to the template when it was created.
Status
Indicates whether the auditing template is enabled or disabled.
Instance
This field is used for filtering data.
Operations
Displays the events selected for auditing on the Events tab of the wizard. Hover your mouse over this cell to view all of the events included in the template.
Filters
Displays the column filters applied to a template.
 

Click the expansion box to the left of the Template name to expand this view and display the following details for each template:

Instance
Displays the name of the SQL instance selected on the first page of the wizard.
Status
Indicates whether auditing for the selected instance is enabled or disabled.
 
* 
NOTE: The cells directly under the main heading rows are used for filtering data. That is, as you enter characters into these cells, the client will redisplay the templates that meet the search criteria (i.e., comparison operator and characters entered). For more details about using the data filtering function provided throughout Change Auditor, see the Change Auditor User Guide.

SQL Auditing templates

To enable SQL Server auditing, you must add a SQL Auditing template to an agent configuration, which can then be assigned to the appropriate agents. Change Auditor for SQL ships with a pre-defined template that you can use to audit key events on the default SQL server instance or you can create a template to specify the SQL instances and SQL Server operations to audit.

Best Practice SQL Auditing template

The Best Practice SQL Auditing template is a pre-defined template that audits the default SQL instance for the following SQL Server operations:

•
Audit Add DB User
•
Audit Add Login
•
Audit Add Login to Server Role
•
Audit Add Member to DB Role
•
Audit Add Role
•
Audit Change Database Owner
•
Audit Change Member in DB Role
•
Audit Create Database
•
Audit Drop Database
•
Audit Drop DB User
•
Audit Drop Login from Server Role
•
Audit Drop Member from DB Role
•
Audit Drop Role
•
Audit Grant Database Access to DB User
•
Audit Revoke Database Access from DB User
•
Data File Auto Grow
•
Data File Auto Shrink
•
Log File Auto Grow
•
Log File Auto Shrink

You can assign this template to an agent configuration or can use it as a base for creating your own SQL auditing templates.

For instruction on how to assign the default Best Practices SQL Auditing template to an agent configuration, see Getting Started.

To create a new SQL server auditing template:
* 
NOTE: You can use the Best Practice SQL Auditing Template as a starting point for creating a template. That is, you can edit this pre-defined template to add additional SQL server operations or define column filters. You can also, add a new instance to the auditing list and copy the pre-defined operations and filters to the new instance. See the To modify a template: procedure for more information.
1
Open the Administration Tasks tab.
2
Click Auditing.
3
Select SQL Server (under the Applications heading in the Auditing task list) to open the SQL auditing page.
4
Click Add to start a wizard which steps you through the process of creating a SQL auditing template.
5
Enter a name for the template and select the SQL instance to audit.
•
Select the Default option to audit the default instance. Select Add to add it to the SQL Instance list.
•
Select the Named option to audit a named instance. Select the browse button, select a SQL instance from the list displayed and click OK to close the dialog. Click Add to add the SQL instance to the auditing list.
•
Select All Instances to audit all the SQL instances on the server. Click Add to add it to the SQL Instance list.
6
On the second page of the wizard, select the operations (facilities or event classes) to audit. You must select at least one event.
Select an entry from the list box at the top of the page, expand the Add button and click one of the following commands:
•
Use the Add | Add This Event button to add individual events.
•
Use the Add | Add All Events in Facility option to add all events in the selected facility.
7
On the third page of the wizard, optionally define column filters to capture only a subset of transactions.
•
Select/highlight an entry from the data grid.
•
In the Filter where fields, enter the operator and value to use in the filter. In the first field (left) use the drop-down menu to select the operator (e.g., Like or Not Like; =, !=, <= or >=). The operators listed are based on the entry selected in the Filters list above. In the second field (right) enter the value or string to use in the filter.
* 
NOTE: You an use the following wildcard characters in LIKE expressions for non-exact string matches:
•
Use an asterisk (*) to match zero or more characters.
•
Use a percent sign (%) to match zero or more characters.
•
Use an underscore (_) to match a single character.
•
Click Add to add it to the Filter list.
* 
NOTE: To add multiple filters, select the column filter row after which the new filter is to be added, and then use the Filter where fields to specify the new criteria. By default, when multiple filters are specified these filters are ‘ANDed’ together and all filters must be met in order to be considered a match. To use the ‘OR’ operator instead, click in the left-most column of a column filter row and select OR from the drop-down. When filters are ‘ORed’ together, then only one of the filters must be met in order to be considered a match.

When both ‘AND’ and ‘OR’ operators are present in the filter list, ‘ORed’ filters are evaluated first and their results are used by the ‘AND’ filter.

8
To create the template without assigning it to an agent configuration, click Finish.
9
To create the template and assign it to an agent configuration, expand Finish and select Finish and Assign to Agent Configuration.
On the Configuration Setup dialog, use one of the following methods to assign this template to an agent configuration:
•
Select the template and drag it onto a configuration in the Configuration list.
•
Select a configuration from the Configuration list and drag it onto the newly created template.
•
Select a configuration, then select the template, right-click and select Assign.
•
Select a configuration, then select the template, click in the corresponding Assigned cell and click Yes.
10
If this configuration is not assigned to any agents, you will need to assign it to one or more installed agents to capture the specified SQL events.
•
On the Agent Configuration page, select one or more agents from the agent list and click Assign.
•
On the Agent Assignment dialog, select the configuration definition to assign to the selected agents and click OK.
•
On the Agent Configuration page, select the agents assigned to use the modified agent configuration and click Refresh Configuration to ensure the agents are using the latest configuration.
 
* 
NOTE: If you do not refresh the agent’s configuration, the agent automatically checks for a new agent configuration based on the polling interval setting (located on the System Settings tab of the Configuration Setup dialog). The default is every 15 minutes.
* 
IMPORTANT: When multiple SQL auditing templates are assigned to an agent configuration, the criteria specified in the last template assigned to the agent takes precedence.
To modify a template:
1
On the SQL auditing page, select the template to be modified and click Edit.
2
This opens the SQL Auditing wizard, where you can modify the SQL instance, events and/or filters included in the template. For example:
•
On the first page of wizard, you can change the name of the template or add a new SQL instance to the selected auditing template.
* 
NOTE: If you add a new instance to the SQL instance list and want to use the same operations and filters that were previously defined for a SQL instance that is already in the list, simply click on the ‘old’ SQL instance and drag it onto the ‘new’ entry.
•
On the second page of the wizard, you can add or remove SQL events from the selected auditing template.
•
On the third page of the wizard, you can add, modify or remove column filters from the selected auditing template.
3
Click Finish or expand Finish and select Finish and Assign to Agent Configuration.
To disable an auditing template:

Disabling a template allows you to temporarily stop auditing the specified SQL instance without having to remove the auditing template or individual SQL instance from a template.

1
On the SQL Server auditing page, use one of the following methods to disable an auditing template:
•
Place your cursor in the Status cell for the template to be disabled, click the arrow control and select Disabled.
•
Right-click the template to be disabled and select Disable.
The entry in the Status column for the template will change to ‘Disabled’.
2
To re-enable the auditing template, use the Enable option in either the Status cell or right-click menu.
To disable the auditing of a SQL instance in a template:
1
On the SQL auditing page, use one of the following methods to disable a SQL instance in an auditing template:
•
Place your cursor in the Status cell for the SQL instance to be disabled, click the arrow control and select Disabled.
•
Right-click the SQL instance to be disabled and select Disable.
The entry in the Status column for the selected SQL instance will change to ‘Disabled’.
2
To re-enable the auditing of a SQL instance, use the Enable option in either the Status cell or right-click menu.
To delete an auditing template:
1
On the SQL auditing, select the template to be deleted and click Delete | Delete Template.
2
A dialog displays confirming that you want to delete the selected template. Click Yes.
To delete a SQL instance from a template:
1
On the SQL auditing, select the SQL instance to be deleted and click Delete | Delete SQL Instance.
2
A dialog displays confirming that you want to delete the selected SQL instance from the template. Click Yes.
* 
NOTE: If the SQL instance is the last one in the template, deleting this SQL instance also deletes the template.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating