ApexSQL Audit SQL auditing paradigm

Having an SQL auditing system in place seems like a straightforward way to harden the security of your SQL data. This way you can at least track the activities and changes done to your SQL Server instance and databases, and depending on the specific auditing system you are using, you might even prevent or roll back malicious or inadvertent changes. However, it’s important to keep in mind that just having a SQL auditing system up and running is not enough. Poorly planned or supervised auditing solutions may lull you into a false sense of security, and actually result in you discovering an information leak or data loss much later than if you had no auditing set up in the first place

The Achilles’s heel of SQL auditing

The weakest point of an auditing system is in fact its most valuable asset – the output that system provides. Namely, one is inclined to trust the data presented by the auditing system. This is perfectly natural reaction – after all, auditing systems are built based on the trust that technology is neutral and as such, the results it presents are impartial. The bad news is that this trust can be exploited by a malicious party – if you take the auditing results at face value (and then again, that’s only natural) the only thing the “bad guy” needs to worry about when covering his tracks is to alter the data presented by the auditing system. This way, when you check the auditing results the system will seem “A-OK” when in fact it might not be. If the attacker made changes to the SQL Server instance, database, objects or data (e.g. dropped a couple of tables or updated sensitive data) chances are these changes will surface sooner or later making you aware that your database security and your auditing system have been compromised. However, if he or she only viewed sensitive information without altering anything, there is a possibility that you are never going to find out about the security breach. The question which naturally arises is: “How can I make sure that my auditing data hasn’t been tampered with?”

How proof an auditing system needs to be to be deemed tamper-proof?

Unfortunately, the short answer to this question isn’t a sweet one – data tampering performed by trusted parties (whether real or impersonated) cannot be prevented. Therefore, no software-only solution can be tamper-proof

Regardless of the extent of the counter-measures you take to prevent auditing data tampering, a sufficiently motivated attacker can find a way to bypass them. For instance, if you store your auditing results in a file a trusted party simply needs to edit the contents of the file before you are able to review it and be in the clear without raising any suspicion. If you store the captured data in a database, the incriminating records could be altered or simply deleted. If you decide to encrypt the database to protect the data at rest, if the malicious user obtains the key you are right back at square one. If you store your data offsite you then depend on external trusted parties but furthermore a “man-in-the-middle” attack can be performed to intercept and alter or discard the captured data before it even reaches the remote database. And so on

On the other side of the spectrum, a privileged attacker might stop the auditing system temporarily and then restart it after the deed is done thus ensuring that his actions won’t be captured. Alternatively, one could corrupt the auditing system (e.g. if your auditing is trigger-based, anyone who obtains ALTER TRIGGER permissions can render them useless) or intentionally feed it bogus information so that it appears that it appears corrupt and thus, untrustworthy

The ApexSQL Audit approach

Although data tampering cannot be prevented – it can be detected. This is the auditing paradigm we’ve built ApexSQL Audit upon. There are three key points to this paradigm:

  • Tampering with the auditing system must be evident
  • Tampering with the captured data must be evident
  • Any attempts to cover up tampering must be made as expensive as possible

In order to achieve these:

  • We capture information used to disclose any attempts to tamper with the auditing system and treat it the same way as the audited data itself
  • We show system downtimes. This way, auditors can be aware of the period the system was out of commission and use that information in their own reports
  • We try to detect corruption early. Deleting, renaming or corrupting ApexSQL Audit binaries and/or other binaries on which it depends is by no means impossible. Any application can be corrupted in such a way to fake its un-corruptness. However, this would essentially require preparing the attack in advance by reverse engineering the entire system – something that is expensive in terms of time, resources and knowledge. Even if such attack occurs it cannot cover up any previous intrusions
  • We’ve built in a series of tamper-evident measures in the design of the Central Repository Database itself. These include row-level hashing and versioning, row group hashing and timestamp mapping among others and ensure that the data that has reached the central repository database cannot be simply updated or deleted without a trace. As these measures are, in a manner of speaking, layered one upon another the complexity of any undetectable attack on the data stored in the central repository database is exponentially increased by each layer

More details on the actual techniques that we use in order to make tampering evident can be found in the Tamper-Evident Design article

What cannot be cured must be endured

As stated in the 10th immutable law of security – technology is not a panacea. There is no software-only solution which can make you data absolutely safe from trusted parties. There isn’t much that anyone can do about an attacker stopping the auditing system, accessing or changing data and objects and then restarting it again. What we can do is detect any outages and inform about them. There isn’t much that anyone can do about an attacker corrupting the auditing system, dropping or changing the captured data or flat out corrupting or destroying the file or database used to store it. What we can do is detect and let you know if there has been any corruption and make it extremely expensive (hopefully more expensive than it’s worth) to tamper with the captured data before it reaches the central repository database and once it’s stored there

Last but not least, an attacker whose intent is to damage the system or cause denial of service doesn’t really care whether he’ll be detected or not as inherently those types of attacks are quite obvious; however subtly tampering financial data or accessing someone’s medical records is almost always meant not to be detected and ApexSQL Audit has been built to shine a light on those

June 7, 2013