SQL Server auditing – initial configuration

Applies to

ApexSQL Audit

Summary

This article provides information about how to configure SQL Server auditing with ApexSQL Audit.

Description

To setup ApexSQL Audit to meet appropriate auditing requirements, the tool and its components for local or remote auditing need to be installed on appropriate machines. To get some help when opting for the most appropriate approach for the specific environments, check the ApexSQL Audit installation guides article which provides key information and links as well as detailed descriptions of all important installation guides which include detailed step-by-step and how-to guides.

Once ApexSQL Audit has been installed into the auditing environment, the SQL Server auditing does not start immediately – the tool must be first configured to audit specific SQL Server events per any local or compliance standard requirements

  1. To start, first, we need to add SQL Server for auditing, so start ApexSQL Audit, click on the Configure button and the main ribbon, and then click on the Add server button

    Add new SQL Server for auditing

  2. Next, choose a SQL Server instance from the drop menu (or manually type server name) and click Add

    Choose the desired SQL Server name

  3. Next, provide username and password with appropriate administrative permissions, tune additional options (if needed), and click OK

    Advanced SQL Server auditing agent configuration

    For clarification on how additional options work see the article: Configuring auditing via the advanced session options in ApexSQL Audit

  4. With this, SQL Server is added for auditing, and we can now proceed and configure which server and database level operations/events we need to audit. There are several different approaches here:

    1. Simple filter
    2. Advanced filters
    3. Out-of-the-box templates based on supported compliance standards

Using simple filter

A simple filter is an easy solution which requires all event that needs to be audited to be checked. To configure SQL Server auditing using a simple filter, once we’ve added SQL Server instances for auditing, we need to do the following:

  1. Select the SQL Server instance we’ve previously added for auditing in the Server panel on the left and check the server-level operations which need to be audited from the list

    Simple auditing filters for SQL Server

  2. At the top of the panel, click on the Applications or Logins buttons to bring in the tabs which will enable you to include/exclude specific windows applications or SQL Server logins in/from the auditing process. Simply choose the appropriate radio button and check applications and logins to include or exclude

    Choose application to exclude from auditing

  3. Next, add databases for auditing by clicking on the Add database button in the server pane. The list of databases is shown, and simply check those that need to be added to the auditing process

    Add new database for auditing

    Chose databases to add for auditing

  4. In the same manner, when server-level operations/events were being added, choose appropriate database-level operations/events for auditing

    Simple auditing filters for databases

  5. It is also possible to specify which database objects will be included/excluded in the auditing task, so click on the Objects button to show the panel which will allow the selection of objects. As before, choose the appropriate radio button and thick checkboxes for specific objects

    Create a scope of database objects to be audited

  6. Optionally, to find and configure monitoring specific columns for sensitive data reads, use the Sensitive columns button:

    Audit sensitive columns

  7. Last, but not least, click on the Apply button to finish the configuration process and initiate auditing

    Apply filter settings

Repeat steps 4, 5, and 6 for each SQL Server database

Quick tip icon

Quick tip:

If the same/similar configuration needs to be applied to multiple SQL Servers or database, instead of configuring each one manually, simply configure the first one and then use import/export configuration to apply it to multiple instances

Using advanced filter

The advanced filter offers greater precision than the simple filter because it is based on combining an unlimited number of logical conditions in order to configure auditing. It is generally recommended to use an advanced filter when there is a need to combine different auditing requirements (e.g. internal requirements together with a compliance standard) or when the need requires auditing of advanced use cases which cannot be achieved via the simple filter

Auditing based on advanced filters differentiates from simple filter based auditing in the fact that server and database levels are not separated as is the case with the simple filters

  1. The first step, again, is to add the SQL Server, as was shown above
  2. Next, click on the Additional menu and switch to the Advanced option to swap to the advanced filter

    Switch to advanced filter option

  3. Next, click on the green “plus” icon to add the first condition

    Add new filter condition

  4. Click on the condition drop menu and choose the one to be used. For example, let’s choose the Application name

    Choose the condition type

  5. Next, click on the operator and choose one from the list. Let’s pick is for our example

    Choose the condition clause

  6. Finally, click on the remaining field and choose the value. We will pick SQL Server Management Studio for our example

    Choose the condition value

    With this, our condition is set – We have opted to audit events that were executed via application “SQL Server Management Studio”

  7. To continue configuring, repeat steps 3-6 as many times as is needed to achieve the desired configuration, and change conditions to fulfill the auditing requirement. Conditions can also be grouped by using the navigation button in front of each condition and bringing them in the same line to form groups

    Set the condition logic

Additionally, here is an example of a more complex advanced filter

Created filter with high complexity using the Advanced filters

The explanation for the logic behind advanced filters can be found in the Advanced SQL Server auditing filters article

Out-of-the-box (compliance) templates

In addition to the fully customizable simple and advanced filters, ApexSQL Audit offers pre-defined compliance-based auditing templates which can quickly be used to import compliance-based configuration on multiple SQL Servers and databases.

  1. Start with adding SQL Server instance(s) for auditing as in the previous scenarios and select the SQL Server in the Servers panel to the left
  2. Next, click on the Template button and choose the appropriate compliance-based configuration from the list

    List of available compliance standards

  3. In the Apply profile dialog, thick the server and/or database(s) which need to be audited for the chosen compliance standard and click Apply. With this, appropriate operations will be included for auditing on the selected server and database(s)

    Choose SQL Servers and databases to apply compliance standard to

After implementing one (or multiple) out-of-the-box configurations, it is possible to further customize/change the SQL Server auditing configuration via both simple or advanced filters to achieve the desired precision.