Change Auditor 7.5 - User Guide

SQL Auditing templates

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 Server 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.

SQL Auditing wizard

SQL Auditing wizard

The SQL Auditing wizard is displayed when you click Add or Edit on the SQL Auditing page. This wizard steps you through the process of creating a new template, identifying the SQL instances to be included in the template. You will also use this wizard to modify a previously defined template.

 

NOTE: A red flashing icon indicates that you have not yet entered the required information. Hovering your cursor over this icon displays a tool tip explaining what needs to be entered.

 

Table 2. SQL Auditing wizard fields and controls

Create or modify a SQL Auditing Template page On the first page of the wizard, enter a name for the template and select the SQL instance to audit.
Template Name	Enter a descriptive name for the template being created.
Audit SQL Instance	Select one of the following options: • Default - This option is selected by default and will use the default SQL instance (MSSQLSERVER) found on an agent that is using the SQL Server Auditing template. • Named - Select this option to use a named instance instead of the default SQL instance. When this option is selected, the name field will be activated allowing you to enter a SQL named instance. Or use the browse button to the right of this field to select from a list of available servers. Selecting the browse button opens the Select a SQL Instance dialog which displays a list of available servers. • All Instances - Select this option to audit all SQL instances on a SQL server.
Add	Use to move the entry in the Audit SQL Instance text box to the selection list. NOTE: Even if you select the Default SQL instance or All Instances, you must click Add to include it in the SQL Instance list.
Remove	Select an entry in the selection list and click Remove to remove it from the template.
SQL Instance list	The list box, located across the bottom of this page, displays the SQL instances selected for auditing. NOTE: If you add a new named 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.
Select the changes in the SQL instance(s) to audit page From this page, select the SQL Server operations (event classes) to audit on the selected SQL instance. You must select at least one operation.
Event Classes	The data grid across the top of the page displays all the SQL event classes available for auditing. Select/highlight an event class and use the appropriate add option to add either the individual event class or all events in the selected facility. This grid displays the following information for each event class: • Facility - the facility to which each event class belongs • Event Class - the events available for auditing • Severity - the current severity level assigned to each event • Status - indicates whether the event is currently enabled or disabled
Add | Add This Event	Use to add the selected event class to the Audit list box at the bottom of the page.
Add | Add All Events in Facility	Use to add all event classes in the selected facility to the Audit list box at the bottom of the page.
Remove	Use to remove the selected entry from the Audit list box.
Selection list	This list box displays the facilities and/or event classes to be included in the selected auditing template.
(Optional) Select column filters page Using the Select Column Filters page you can optionally define column filters to limit the data retrieved. These filters allow you to capture only the required information in high traffic databases.
Filters	The data grid across the top of the page displays the SQL columns available for filtering. Select/highlight an entry and then use the Filter where fields to define the operator and values to be used in the filter.
Filter where ...	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 be used in the filter. NOTE: Valid wildcard characters that can be used in LIKE expressions for non-exact string matches include: • Asterisk (*) to match zero or more characters • Percent sign (%) to match zero or more characters • Underscore (_) to match a single character For example, to limit the data retrieval to all databases that begin with ‘Change’ (e.g., Change Auditor, ChangeAuditor_Archive_2011, ChangeManager, etc.) • Select DatabaseName from the Filters list. • Select LIKE in the first field. • Enter Change% in the second field. • Click Add to add it to the 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. NOTE: 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.
Add	Use to move the filter entered above to the Column Filter list at the bottom of the page.
Remove	Use to remove the selected entry from the Column Filter list.
Modify	Use to change the operator or value of the filter selected in the Column Filter list.
Column Filter list	This list box displays the column filters defined for this SQL Auditing template.

SQL Server event logging

SQL Server event logging

In addition to real-time event auditing, you can enable event logging to capture SQL Server events locally in a Windows event log. This event log can then be collected using Quest InTrust to satisfy long-term storage requirements.

For SQL Server events, event logging is disabled by default. When enabled, only configured SQL server activities are sent to the Change Auditor for SQL Server event log. See the Change Auditor for SQL Event Reference Guide for a list of the events that can be sent to this event log.

To enable SQL Server event logging:

1	Open the Administration Tasks tab and click Configuration.

2	Select Agent.

3	Click Event Logging and select SQL.

4	Click OK to save your selection and close the dialog.

SQL Data Level Auditing

SQL Data Level Auditing

•	Introduction

•	SQL Data Level Auditing page

•	SQL Data Level Auditing templates

•	SQL Data Level Auditing wizard