Inability to ensure tamper-proof auditing is a major flaw of many SQL Server auditing solutions available on the market. While there are many solutions and different tools that utilize different mechanics to provide the information on who saw or changed what, when and how, most of Microsoft’s own, as well as the majority of third-party solutions do not provide any mechanisms or features to protect the audited data or to achieve tamper-proof auditing. This immediately raises a red flag because many internal, as well as external auditors, make it a mandatory requirement for auditing solutions to be able to provide certain levels of data protection for the audited data as well as a self-auditing mechanism for the solution itself, which will ensure that the auditing reports for auditors are 100% genuine and that audited data can be identified as true, untampered and uncorrupt.
In this article, we are going to take a look into ApexSQL Audit, and its unique tamper-proof auditing mechanisms.
ApexSQL Audit is a SQL Server auditing tool that allows users auditing of almost 200 SQL Server events, including the structure and data changes, access to database objects and data, as well as security-related events on both server and database levels. It allows users auditing of multiple SQL Server instances while keeping all of the audited data into one single central repository database – a perfect setup for tamper-proof auditing concept which makes it much easier to pay attention to a single database storing all of the audited data vs some other solutions which use multiple repository storages that are not particularly good for tamper-proof auditing implementation.
Before focusing on how tamper-proof auditing is achieved, let’s first define some basic tampering scenarios:
- Auditing instance tampering
- Data tampering
Auditing instance tampering
The first line of defense in any scenario is, of course, making sure that the source of auditing is safe from any/most internal breaches, which are focused on the core – an auditing mechanism and services. While in the vast majority of cases, highest level access on both Windows and SQL Server levels is reserved only for the most trusted parties, in some instances, other users or applications may have this highest level of clearance and can be potentially used as the tool for the breach. Furthermore, those with the highest privileges often find all the doors open and can indeed make changes that will affect the auditing. These include removing Windows services or processes or even uninstalling auditing solution, blocking network access or removing permissions to the users that run the auditing solution, deleting or tampering with the auditing solution files, including both configuration files and files containing audited data and more – there are truly hundreds of possible targets for privileged users.
Yet, with great power comes great responsibility, and having privileged users or users with the highest access permissions tampering with the data is a rare occasion. Nevertheless, ApexSQL Audit has many different self-auditing mechanisms that will allow us to track many different events that could potentially jeopardize auditing job and can alert users on such critical events and help us ensure tamper-proof auditing. Some of the main self-auditing mechanisms are:
- Auditing (Windows) service timestamps
- Information on creation and closure of files that contain audited data
- Start and stop of internal service
- Heartbeats, warnings, and errors (for service)
- Start and stop/completion of collections for audited files (Traces, Extended Events)
- Loading information on audited data files
- Information on quarantined files
- Any changes, removal, or updates to the auditing configuration on both server and database levels
- Information on performed data integrity checks
As an addition, some built-in alerts can notify us immediately if there are environment issues like:
- Low disk space
- Permission problems
- Connection problems both on Windows and SQL Server levels
- Failure to receive audited data in specific time frames
- And more…
In most of the above-mentioned areas, which can affect auditing on the core level, the information captured by ApexSQL Audit will contain a plethora of details which can enable us to identify the following:
- What happened?
- How was the auditing affected?
- Who did it?
- When?
- How?
- What are the consequences?
- And more…
In the screenshot below, we can see an example on several configuration changes (for audited events) on the SQL Server:
And in the next one, we can see information on a purge that was performed and deleted a number of events (361) from the short time frame (3 seconds):
Without the solid tamper-proof auditing solution, inadvertent (due to mistake) or potentially malicious changes to the auditing mechanisms and data can be omitted with huge consequences and risks.
Data tampering
In the previous section, we’ve focused on internal breaches of tamper-proof auditing and how important it is to ensure that any changes to the environment that can affect auditing job must be made transparent and identified early in order to ensure a high level of auditing and being able to provide the auditor with full, uninterrupted and genuine auditing trail. Now, let’s move to the next level and the second scenario – tampering on the data level. While the auditing instance is focused on ensuring that the auditing flow is uninterrupted and that we audit exactly what we need to, we are focusing on the pre-auditing aspect.
On the post-auditing aspect, we must ensure that our audited data is safely stored and that any changes to it will be evident and appropriate flags raised on any breach or potential tampering. The ‘easiest’ tampering attempt is to pretty much delete the audited data directly from the repository database. Another one is to add rogue rows (INSERT) to the database that would change the data set or perform an update of data to directly change existing field values in tables. Extension of this approach would be to use SQL Server mechanisms to restore the database to point in time (backup) negating and removing hours/days/months of audited data – and these are just some of the potential hazard, but all in all, include ~95% of tampering attempts between themselves – both successful and failed.
From the safety perspective, it is not possible to have mechanisms to defend against all these attacks because, in some cases, data can be completely overwritten or lost, and while some measures to defeat tampering can be imposed on Windows and SQL Server levels, winning the war here is impossible.
What we can do is to ensure that for our tamper-proof auditing solution we add as many tamper-evident measures that we can, which will always be able to alert us on any potential tampering events, help us investigate what happened, and provide as many details as possible on the attempt to prevent future breaches from the specific party, area, application or more.
ApexSQL Audit relies on SHA-256 hash to add unique hash to each data entry to the central repository database. Furthermore, row-level hashing of all known field values is created when the data row is being inserted. More details on this can be found in the ApexSQL Audit Tamper-evident design features article.
In plain language, all rows have a unique hash, and each consecutive hash will utilize the previous one and incorporate it when being created. Further translated, this means if any row field is changed, the hashed value will be changed as well, ‘breaking’ all consecutive hashes, and making it easy for us to pinpoint tampering to the exact row. Furthermore, the tamper-evident design of ApexSQL Audit repository and internal auditing mechanisms again allow us to identify who exactly changed the data, when, and how, as well as what was changed.
Data integrity verification
We’ve seen the protection mechanisms of our tamper-proof auditing solution – ApexSQL Audit, now let’s quickly focus on integrity checks. We’ve already mentioned automatic notifications and alert on potential tampering events, but let’s quickly look at how ApexSQL Audit handles these. The ‘Central repository database tampering alert’ is a built-in alert that will trigger on each and every change on the audited data or the repository database itself and will provide information and details on the event.
Furthermore, by using the internal ApexSQL Audit mechanism, this notification can be automatically sent to any given email addresses using SMTP as a medium for sending mail.
Naturally, integrity checks can be performed manually as well, at any given time – something that auditors will very much be interested in – to ensure that the data is genuine:
Additionally, due to the fact that ApexSQL Audit can create archives of the central repository database to keep the data safe and adding to the agility by not forcing users to store all the historic data at the auditing server at all times, it is important to know that all of the archives that are no longer in use can also be checked for any potential tampering, and can be verified for data integrity adding an additional level of security and assurance to the auditors.
To summarize – while there is no tamper-safe software solution which could guarantee 100% safety of audited data, in order to prove to auditors that audited data integrity is true and that data has not been tampered with, ApexSQL Audit provides solid tamper-proof auditing mechanisms which add a layer above the layer of mechanisms that will track any changes to both internal core components, external sources and audited data itself and make it very transparent that potential tampering has occurred, or that the auditing job integrity has been compromised, and provide a plethora of information on the event making it easy to identify where the attack came from, who orchestrated it and more.
April 24, 2020
