An overview of SQL auditing features in ApexSQL Audit (4263003)

This article will provide an overview of features for SQL auditing via the ApexSQL Audit application and highlight some advantages when using them.

ApexSQL Audit is a SQL Server auditing and compliance tool that can ensure full SQL auditing by collecting almost 200 types of SQL Server events including real-time alerting. All audited data is stored in a central repository database with data integrity checking. Predefined built-in and custom reports allow quick and easy data presentation.

This article intends to provide an overview of some important aspects of the tool that give advanced SQL auditing capabilities.

One of the many reasons there’s no need for a 100-page help file for ApexSQL Audit is the time and effort put into curating the fastest and easiest installation process possible. What little configuration remains is simple, straightforward, and self-evident thus preventing the installation process to be a time-consuming and frustrating task.

The installer is designed to work on both 32-bit and 64-bit operating systems, avoiding an unnecessary branching decision by users, or having to work with two sets of installers. It is packed in the installer file that hits the ground running, by temporarily unpacking files and auto-starting. No advanced IT/SQL Server knowledge is required nor excessive branching decisions are demanded. Cryptic error messages are avoided where possible and as much as possible, e.g. “Error -2147217900: failed to execute”.

The installation requires minimal user interaction. During the installation process, the installer itself will perform most of the necessary actions on behalf of the user. The only actions required by the user when installing the main application is to select installation options and to enter the required credentials for the account that will be used to access the central repository:

• Detailed explanation for the installation process can be found in the article: Installing ApexSQL Audit

Setting up the required firewall exceptions on behalf of the user is also the automated part of the installation process.

Installation of the server-side components or ApexSQL Audit standalone GUI is also straightforward and while there is no need for any input when installing server-side components, when the standalone GUI is being installed, only the location of the central repository database needs to be specified. After the installation, the application is fully functional and can be used immediately.

• Details about deploying components on a remote machine can be found in the articles: How to install ApexSQL Audit main GUI on a remote machine and Remote installation of ApexSQL Audit auditing instance (service)

SQL Server and Windows OS support

ApexSQL Audit fully supports auditing of all SQL Server versions starting with SQL Server 2005 and all Windows versions starting with Windows Server 2008/Windows Vista. If a new SQL Server version is released, the application will have support for it within 30 days of release.

The central repository database can be stored on all SQL Server versions starting with SQL Server 2008, and there are no limitations regarding which SQL Server version can be audited in regards of SQL Server version used for storing the central repository database.

• Details about supported software can be found on the What’s supported page

Simple filter

Basic filtering abilities include not only very granular filters but value-added options like the ability to exclude, in addition to include, as well as the ability to set different filters for different servers and even databases.

The application allows users to select or not select each individual SQL Server event on the server and database level. Unique SQL auditing filters for each database can be set individually.

Below is an example of a custom set of events that can be selected for SQL auditing on the server level:

And a list of custom set of events that can be selected for SQL auditing on the database level:

The simple filter offers the ability to include/exclude applications, logins, and SQL Server objects from the auditing process:

Advanced filter

The advanced filter, unique to ApexSQL Audit, allows for a high level of auditing precision of the specified audited events data. It uses a set of logical operators that can be chosen to define the precise auditing filter condition up to the T-SQL level. This is also an optimal design for usability, in that complex filters can easily be built intuitively.

• More information and details can be found in the ApexSQL Audit Feature highlight: Advanced filtering article

Before-after auditing

In addition to ‘regular’ SQL auditing, ApexSQL Audit allows configuration and setup auditing of table field values before and after a specific change. This feature allows to fully audit both before and after change values for insert, update and delete operations.

• For more information on before-after auditing, please visit this article

Sensitive columns

It is possible to monitor and audit read access over columns containing sensitive data. The feature provides one-click search, detection, and selection of sensitive columns across the database using built-in or custom definitions of what might be considered as sensitive.

• For instructions on how to use sensitive columns feature see the article: Configuring sensitive columns auditing in ApexSQL Audit

Reports

One of the distinctive advantages of the ApexSQL Audit is the “open” reporting system which doesn’t limit or restrict output and offers the possibility of CSV, Excel, Doc and PDF exports.

There are no limitations on the number of events that can be displayed in the report, and with that in mind, the CSV export provides a significant advantage when reviewing exported results.

Out-of-the-box but still configurable reports

ApexSQL Audit utilizes built-in reporting profiles capable to meet most of the reporting requirements on audited data. These profiles correspond to most of the SQL auditing compliance standards and can be used directly out-of-the-box, but can still have a high degree of customization to change them if needed vs offering a fixed, all or nothing approach to reporting:

Fully custom reports

Customized reports were designed with the intention to meet even the most demanding reporting requirements by the ability to create totally new, fully customized reports from scratch. The custom filter utilizes the same advanced filter form as the advanced auditing filter.

The custom reports feature is designed to create a filter condition that can be configured to meet any specific reporting needs. Every defined filtering condition can be named, saved, and re-used again when needed.

There is no limitation on the number of custom reports that can be created and saved.

Below is the example of a custom report:

• The feature functionality and usage is detailly described in the article ApexSQL Audit reporting feature overview

Scheduled reporting

Reporting can be executed on-demand or automatically, by timed schedule. This is the best way to envelop periodically audited data without using reminders and deliver them directly to the person(s) of interest via email.

Any and multiple saved report templates can be executed unattended, saved in the desired format, and delivered automatically upon generation.

• For instructions on how to configure this function see the article: How to create a scheduled audit report with an e-mail notification

• ApexSQL Audit standalone GUI

  The application GUI offers centralized SQL auditing administration, as well as reporting of audited data. It eliminates the need for an RDP connection for those purposes as it can be used as remote console with the capability to delegate the access levels across assigned users thus increasing both security and comfort.

  Data alerting

  The SQL audit alerting system offers true real-time alerting as the application alerting engine intercepts the audited events and processes them in accordance with defined conditions at the same time the central instance receives the information. This allows the alert to be triggered in real-time often before the actual event is even stored in the repository database. Furthermore, alerts can be configured to send an email notification via an SMTP server to specific email addresses once the alert has been triggered to alert users on the specific event.

  The SQL audit alerting system has the following list of capabilities:

  • The alerting engine utilizes the same advanced filter used for the auditing filter and custom reports to ensure creating the highly precise alerting conditions
  • Alerting when the user-specified string is detected in the T-SQL statement which is executed against the audited SQL Server via implemented T-SQL parser engine
  • Alert email notifications allow defining a recipient’s email address separately for each new alerting condition
  • No limitations in how menu alerts can be defined and used at the same time
  • No limitations in the number of alerts that can be stored and displayed in Alert history
  • The ability to display the full T-SQL of an event in the alert message body
    • The ability to send email notification on triggered alert

  

  • More details about how to manage and utilize alerts can be found in the articles How to create a custom alert with an email notification and Inventory of system alerts for SQL auditing

  Encryption level

  ApexSQL Audit utilizes the strongest hash encryption with a chaining algorithm available for archived data. SHA-256 encryption generates an almost unique, fixed 256-bit (32-byte) in size hash. The hash encryption, as a one-way function, cannot be decrypted back, which makes it the best choice for anti-tampering of the central repository database and efficient data integrity verification.

  Tamper evident repository database design

  Preventing unauthorized or inappropriate modification of the central repository database is essential, but the application provides the same strong tamper-evident design implementation for the archive databases as well.

  Even data integrity of the archived data can be checked, in the same manner as when working with the central repository database.

  This eliminates a significant burden on end-users to organize the archived data safeguarding and integrity checking by themselves.

  • The usability of the feature is explained in the article: Verifying SQL audited data integrity of central repository database

  Archiving

  ApexSQL Audit has a unique option to archive the full central repository database, which leaves it completely empty after archiving. This is very important if SQL Server Express is used for hosting the central repository database. Coupled with system alerts that allow the user to define the maximum size of the central repository database, even using the Express edition of SQL Server, with all of its limitations, is a problem-free experience with the application.

  Maintenance

  Data retention is ensured for an indefinite period. If this is not a requirement the data can be removed entirely or selectively for a specified period. Data is purged specifically per archive, which is not in use anymore, so the possibility to accidentally remove the wrong data period is eliminated.

  The Repository maintenance options article explains how to handle unnecessary retained data.