Advantages of ApexSQL Audit

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

The intention of this article is to provide an overview of some important ApexSQL Audit advantages over similar tools and at the same time to provide insight in some unique features specific to ApexSQL Audit.

Installation

One of the many reasons we don’t need a 100-page help file for ApexSQL Audit, is the time and effort that we put into curating the fastest, and easiest installation process possible. What little is remained is simple, straightforward, and self-evident.

We avoid a lot of the “pain points” of other tools that can make installation a time consuming and frustrating process.

We have a very small installer that is designed to work on both 32 bit and for 64 bit operating systems, avoiding an unnecessary branching decision by users, or having to work with two sets of installers.

We’ve crafted an installer file that hits the ground running, by temporarily unpacking files and auto starting. Other tools require unnecessary steps just in unpacking the installer and require post install “housekeeping” to clean up files.

We don’t require advanced IT/SQL Server knowledge or offer excessive branching decisions. We also avoid cryptic error messages that are not infrequent with installing other tools e.g. “Error -2147217900: failed to execute”.

ApexSQL Audit 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 by the central instance.

Setting up the required firewall exceptions on behalf of the user is also the part of installer options.

Options like these aren’t available in competitor tools and in their case, creating the specific user in target SQL Server and creating the firewall exception in the host Windows OS must be performed manually by the user before and after the installation process, to ensure that application can be used after installation.

Installation of the server-side components or ApexSQL Audit reporting application is even more straight forward and while there is no need for any input when installing server-side components, when ApexSQL Audit reporting application is being installed, users only need to specify the location of ApexSQL Audit central repository database.. After the installation, ApexSQL Audit is fully functional and can be used immediately.

SQL Server and Windows OS support

ApexSQL Audit fully supports for auditing all SQL Server versions starting with SQL Server 2005 and all Windows versions starting with Windows Server 2008/Windows Vista. ApexSQL supports new SQL Server generally within 30 days of release.

ApexSQL Audit can store the central repository database 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. This is a critical limitation with other tools where storing the repository database on SQL Server version lover than Audited SQL Server version is not allowed.

Simple filter

Competitors’ tools utilize some basic filtering abilities at the aggregate level like Logins, Security, DDL, DML changes, administrative activities etc.

Our customers have indicated a strong preference for not only, more 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

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

Below is example of the individual SQL Server events that can be selected for auditing on the server level…

And a list of individual SQL Server events that can be selected for auditing on the database level.

The ApexSQL Audit Simple filter offers the ability to include/exclude applications, logins, and SQL Server object from the auditing process.

Advanced filter

This advanced filter, unique to ApexSQL Audit, allows for a high level of auditing precision of the specified audited events data. The Advanced filter 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 in an intuitive way.

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

Before-after auditing

In addition to ‘regular’ auditing, ApexSQL Audit allows users to configure and setup auditing of values before and after a specific change. While most of the competitors do not provide auditing of events for before and after values, some competitor tools can show a after-change value. ApexSQL Audit provides a feature 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.

Reports

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

ApexSQL Audit has no limitation on number of events that can be displayed in the report. Some other tools limit to a fixed number of events.

ApexSQL Audit can display the events text data (T-SQL executed behind the event) of unlimited size, which mean that even the largest and most complex statements can be fully previewed and exported. Other tools set limits on the number of characters.

ApexSQL Audit has another advantage in the use of the CSV file format (amongst others) for exporting reports to a file. The CSV export, combined with the lack of fixed export limitations provides a significant advantage when reviewing exported results.

Out-of-the-box but still configurable reports

Like other tools, ApexSQL Audit utilize built-in reports capable to meet most of the reporting requirements on audited data.

What makes these out-of-the-box reports different and more advanced than similar competitor’s implementations, though, is the flexibility and customization that exists via the comprehensive filtering options that are available.

In this way, users can enjoy pre-built and configured reports, but can still have a high degree of customization to change them if needed vs offering a fixed, all or nothing approach to reporting.

In addition to commonly used reports, ApexSQL Audit offers predefined reports based on all supports compliance standards – HIPAA.

Fully custom reports

Customized reports were designed with intention to meet even the most demanding reporting requirements by the ability to create totally new, fully customized reports by 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 in number of custom reports that can be created and saved

Below is the example of a custom report. For more detailed information is the ApexSQL Audit Feature highlight: Custom reports article

ApexSQL Audit reporting application

Standalone reporting application allows creation of auditing reports from any computer with the local area network or inside the domain. Competitors either lack separate reporting application, or their implementations lack the same feature set as can be found in the main application. Furthermore, most competitors lack possibility to export/import report configuration and exchange them between the main application and reporting module, while ApexSQL Audit reporting application allows full report-related features as can be found in the main application.

Data alerting

The ApexSQL 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 receive the information. This allows the alert to be triggered in real-time often before the actual event is even stored in the repository database. This is not the case with most competitors where alerting system is triggered after the data arrives at the repository, which, in some environments, could involve significant lag time. Furthermore, ApexSQL Audit can be configured to send an email notification via SMTP server to specific email addresses once the alert has been triggered to alert users on the specific event.

The ApexSQL Audit alerting system has features that either are unique or are highly advantageous vs other tools:

  • 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 event in the alert message body
  • • The ability to send email notification on triggered alert

Encryption level

ApexSQL Audit utilize the strongest hash encryption with 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.

Other tools use 64-bit encryption which is generally considered weak and relatively easy to break today. It just isn’t sufficient for most enterprise level security considerations.

Tamper evident repository database design

Preventing unauthorized or inappropriate modification of the central repository database is essential, but ApexSQL Audit provides the same strong tamper evident design implementation for the archive databases as well. With most competitors, the archived database protection is relegated to the user to manage.

ApexSQL Audit can even check data integrity of the archived data, 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.

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 for users who want to use SQL Server Express for hosting the central repository database. Coupled with system alerts that allows the user to define the maximum size of the central repository database, a problem-free use of SQL Server Express database version is ensured.

November 10, 2015