Auditing SQL Server databases – initial configuration

Summary
This article provides information about how to configure ApexSQL Audit in order to audit SQL Server instances and databases.

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 to 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 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



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



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



  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

Simple filter is an easy solution which requires all event that need to be audited to be checked. To configure SQL Server auditing using 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



  2. In 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 chose the appropriate radio button and check applications and logins to include or exclude



  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.





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



  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 selection of objects. As before, choose the appropriate radio button and thick checkboxes for specific objects.



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



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

Quick tip icon

Quick tip:

If 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 unlimited number of logical conditions in order to configure auditing. It is generally recommended to use 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 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 Advanced button to swap to the advanced filter



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



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



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



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



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

  7. To continue configuring, repeat steps 3-6 as many times as is needed to achieve desired configuration, and change conditions to fulfil 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



    Additionally, here is an example of more complex advanced filter



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 Compliance button and choose the appropriate compliance-based configuration from the list



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



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