Applies to
Summary
This article will explain the capability to verify the integrity of the collected SQL auditing data in order to find tempering evidence in the central repository database.
Details
In a situation where we need absolute certainty that the collected audited data hasn’t been tampered with, ApexSQL Audit offers the functionality to verify this. The Verify option is especially useful in cases where archived audited data repository was re-attached for review after some “shelf” time.
Built-in tampering alert
As part of the ApexSQL Audit’s built-in alerts, an item exists that is configured and enabled by default to alert a person who conducts an audit that there is a breach in the SQL audit data integrity:
Let’s examine the situation where some registered events are deleted from the repository database, which can happen on both, active and archived database:
The alert will be triggered, and the tampering event can be found in the alert History view:
With the default message, “You are advised to re-run a tampering check in GUI and analyze the results” that advises the users to continue with further investigation.
However, due to the necessity for resource-consuming hashed recalculations (especially with larger data collection environments), tampering will be detected only for the active central repository database, and the alert will not be triggered in case some archive was corrupted.
Repository database verification
When tampering alert happens, additional details about what happened can be found using the Verify option in the Central repository section of the Home tab in the main ribbon menu.
By choosing the Verify option, a new main window will show the Database section with the list of all central repository databases, archives included, that can be scanned for an integrity check, i.e., tampering evidence.
Naturally, this option is available regardless of tampering alert notification, so it can be used on-demand, among else, to verify older, detached archives if needed:
This list is based on the list of data sources included in the Reporting tab of the Options window. Here, an archived database can be taken from the “shelf” and attached for examination (for reporting purposes as well as for verification purpose), providing that it was already attached to a SQL Server instance:
-
More detailed information about archive management can be found in the Archiving the Central repository database – ApexSQL Audit article.
When the data sources list is prepared, the verification process can begin.
From the Verify view in the Database section, check all central repository databases that should be scanned and click the Start button:
At this point, the verification process will start, and the tampering incidence can be observed. Since this process can take a while for larger archives, it can be stopped at any time to free up the resources for further work:
When verification ends, in the Database section, it will be possible to see the status for every scanned repository database, and in the main grid view, the list of tampering events for each database affected:
The list of tampered events will provide the information about what type of collected data was affected, which row in the data was affected, and what was the time and type of the audited event which is now corrupted:
With this information provided, it will be possible to conduct further investigation and locate the cause of this SQL audit data corruption.
