ApexSQL Audit is a SQL Server auditing and compliance tool that can ensure full auditing of SQL Server instances by collecting more than 200 SQL Server events including the real time alerting. All audited data are 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 for ApexSQL Audit
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 and optionally by the web reports.
If needed, the installer can even create the SQL Server login for the selected user with necessary privileges in the target SQL Server instance that was chosen to host the repository database.
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 have to be performed manually by the user before and after the installation process, in order to ensure that application can be used after installation.
Installation of the auditing instance is even more straight forward and only the credentials and address of the central instance are required from user. 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-90 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
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 the particular application, logins, and SQL Server object from the auditing process
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
The one of distinctive advantages of the ApexSQL Audit “open” reporting system which doesn’t limit or restrict output and offers the possibility of CSV export
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 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
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
The web-based reporting in ApexSQL Audit permits creating of auditing reports from any computer within the local area network using a web browser. Competitors either lack web reports entirely, or their implementations lack the same feature set as their windows-based reports. ApexSQL Audit web reports are full equals in terms of features and customization as their windows equivalents.
ApexSQL Audit Web reports shares the same ability as the windows based reports including built-in reports and a custom report designer.
The ApexSQL Audit alerting system offers true real-time alerting as the application alerting engine actually 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
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
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
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