How to achieve high quality SQL auditing trail with the minimum number of auditing events

Applies to

ApexSQL Audit

Summary

This article will explain how to optimize SQL auditing settings to reduce the noise in collected data, improve performance and reduce storage consumption

Description

Configuring auditing in ApexSQL Audit is a quick and easy task. One simply needs to define which specific SQL Server events should be audited on both server and database levels, as can be seen in the Auditing SQL Server databases – initial configuration article and then apply the configuration to complete the setup process. SQL auditing will immediately commence, and user can then query their central repository databases to create comprehensive reports and use various filters to ensure they can produce high-quality and precise reports.

Nevertheless, even though excess information can be filtered out in the reports, the better practice is to not audit events with zero or minimal auditing value at all, since excess auditing may result in various additional expenses, such as:

• Longer processing time when storing audited information into the central repository
• Higher disk space requirements – which can take especially heavy toll in the high-traffic SQL Server environments, especially in cases when traces created by ApexSQL Audit have to be temporarily stored on the local drive in case of higher priority SQL Server processing
• Slower report creation due to more complex filters
• Time investments when creating a new report every time, due to the requirement to remove excess data

With the above in mind, there is no reason why ‘all’ events should be audited in any case and add unnecessary encumbrance on their auditing or production environments. In this article, we are going to show how to use different SQL auditing tool’s assets, including auditing filters and features that will allow high-end auditing jobs to be put in motion over different SQL Server instances with minimal auditing trails ensuring the best performance and lowest requirements.

Server filters

Once the application is installed, SQL auditing does not commence immediately – the auditing filters must first be configured.

First, add SQL Server instance for auditing by choosing SQL Server for auditing from the drop menu in the Server pane in the Configuration tab (or typing the name manually) and clicking on the Add button:

Also, provide appropriate credentials and choose connection options before completing the process.

Now, specific server-level events can be added by checking the checkboxes next to each server event in the Operations tab:

Next, on the Applications tab, it is generally advised to exclude all changes made through the applications which are not required for SQL auditing. For instance, if you have an application performing frequent logins, checks, or changes which is not part of the auditing requirements, be sure to exclude those in this filter by checking them in the applications tab

Quick tip: If the application, login or object name is not shown in the displayed filter lists, type its name in the Custom field, click Add and ensure item is checked in the list to add this specific filter:

Another important filtering tool is the Logins filter. Here, we can choose to include or exclude any SQL logins which do not need to be audited per existing requirements (usually, system type logins are not necessary to monitor). As before, simply check specific logins, or add custom ones, and opt to either include or exclude them by choosing the appropriate radio button:

Database filters

Adding databases for auditing is also simple and easy. As before with the SQL Servers, in the Server pane, first select the SQL Server instance which hosts the database, and click the Add database button:

Now, in the Add databases pane, check databases to apply SQL auditing to, and click on the Add button:

As before, it is required to check the database events which will be included in the auditing task:

One of the most frequent mistakes made when an auditing job is being set is skipping the Objects filter on the database levels. More often than not, there are hundreds or thousands of tables, triggers, stored procedures, and other database objects with a vast amount of traffic which is frequently irrelevant for the auditing requirements and should be hence excluded to minimize the trail and traffic size. Choosing to include only a specific object in the auditing task can decrease the SQL auditing trail tenfold or more in most environments.

To configure object filters, as was the case with already mentioned filters, check appropriate objects and opt for a radio button which will allow you to include or exclude them from the auditing job:

Quick tip: A great starting point for auditing configuration are predetermined compliance templates based on the most existing compliance standards such are GDPR, HIPAA, PCI and more. These include minimal auditing events to meet compliance standards, making them ideal starting points before adding more operation, application, login or object filters, both on server and database levels:

Advanced filters

Now that we’ve seen how to use various filters to control the SQL auditing output using simple filters, let’s take this job one step further, straight to the advanced auditing filter.

Advanced filters in ApexSQL Audit take a different approach and combine both server and database levels together. They are created by combining an unlimited number of different conditions to filter the auditing trail. They can be accessed from the Additional menu by clicking on the Advanced radio button and cannot be used together with the simple filters – these will be converted and overwritten.

Conditions are added by clicking on the green ‘plus’ sign and can be combined in various ways. Different operators, grouping and more can be used to achieve pinpoint precision and ensure that the auditing trail is indeed minimal. With the advanced filter, filters can be configured to audit specific database or server events for one login, and completely different operations for another one, why monitoring others only for the third set of events, etc.

• More on how ApexSQL Audit advanced filters can be used and configured for highest auditing precision is described in great details in the Advanced SQL auditing filters article which also offers several great tips on how to group and combine conditions

Before-after auditing

In the Before and after auditing in SQL Server article we show how to set up and configure before-after auditing on a table column level to capture changes before and after the insert, update or delete operation has been executed. DML changes audited by before-after auditing are usually the most frequent changes in the databases since they actually feed and change the data in the database tables. With this in mind, it is important to give some special attention when configuring auditing of DML operations since incorrect configuration can get out of control pretty quickly.

The first thing to keep in mind when configuring before-after auditing is that it goes all the way up to the column-level, which means that we can include or exclude only specific columns in our auditing job, minimizing the output this way. So, first use the Add table button to include specific tables in the configuration, and then dive deep down to the column levels and choose which one will be part of the auditing job:

The second thing to keep in mind regarding before-after auditing is that it can combine the advanced filter with the table filter above. So, once the column-level configuration is completed, we can add conditions to further filter our auditing. For example, we can choose to only audit operations on our included table columns in a case when a SQL Server Management Studio is used by a particular SQL login connecting from a specific client host:

With this, we conclude our SQL auditing configuration recommendations and best practices.

Keep in mind that you may not be able to achieve minimal auditing trails immediately and that some tweaking will be needed. So, a good practice would be to:

1. Configure auditing filters for minimal auditing trail as per known requirements
2. Run SQL auditing reports to investigate auditing trail and isolate any events which may not actually be required and can be removed

3. Keep a watch for any ApexSQL Audit alerts which may indicate huge growth of the central repository database and alert you on potential flaws

   • More on how to leverage alerting to increase the quality of your auditing trail can be found in How to create a custom alert with an email notification article

4. Manually create or schedule regular auditing reports to be automatically created on a predetermined frequency to keep track of your auditing trail. If the latest report is several times larger than those before it, some changes have probably occurred in your SQL Server environment which could be a flag to check and appropriately adjust your auditing filters to accommodate for the latest changes

FAQ

How can I ensure my auditing job does not impose impactful performance hinderance on my production environment?

Aside from checking or keeping track of any potential CPU or memory usage in low-performance environments, it is a good practice to check if traces (including audited data) created by ApexSQL Audit are timely processed by SQL Server, or if they are piling up in the temporary folder (default: c:\ProgramData\ApexSQL\ApexSQLAudit\Auditing\<SQL Instance Name>\ActiveTraces\), which can be a flag that the audited data is being added faster than it can be processed which suggests an immediate reevaluation of the auditing configuration.

What is the expected performance impact of ApexSQL Audit on central and audited instances?

ApexSQL Audit has a minimal performance overhead, equivalent to running the default trace which means it can audit huge amounts of events with minimal/no performance impact.