One thing that Testers need to keep in the back of their heads these days is how the system they are working on is going to be audited to ensure compliance with such things as SOX, Basel II, HIPPA etc.

To meet these standards, you need to be able to:

  • Provide meaningful audit reports on a regular basis to ensure that appropriate controls are in place, and are being enforced
  • Provide an ‘on-demand’ audit trail identifying who did what, to which database objects, and when it was done
  • Detect breaches to defined controls and alert appropriate authorities
  • Retain audit data for a sufficient time to comply with mandated data retention standards – often many years
  • Provide flexible access to audit data for forensic analysis
  • Meet all of the above requirements while not substantially impacting performance on production servers

Some approaches that have not worked as well as they might have in the planning stage are

  • Produced large amounts of script generated snapshot reports hoping that auditors would be satisfied by volume rather than meaningful content
  • Imposed unacceptable load on production servers
  • Been highly complex to implement and costly to maintain as they have required an undue amount of DBA effort to install, configure, and administer on an ongoing basis
  • Been unable to provide a ‘trusted’ source of audit data to external auditors, bringing into question the integrity of the entire auditing process

If your group is following the any or all of the above means of providing audit data, perhaps it might be worth looking into a continuous auditing approach. Center for Continuous Auditing, Rutgers University.

According to Idera, there are “7 Steps to Successful SQL Server Auditing”. Click here for the white paper that all this post is based on. Flipping this around, here are “7 Steps to Successful SQL Server Audit Testing”.

  1. Define Controls – Before you can audit, you need to know what to audit. If your application is to be use in an audit sensitive environment, your test team suddenly needs to be clued in on the standards/requirements that are in scope. This means training for all team members; perhaps general training for everyone on all standards, then each team member recieve moer advanced training on a different one. Internal and External Auditors need to also be a phone/email away to answer any questions. If you do not have this then you are really stumbling in the dark.
  2. Design the Compliance Environment – This is turning the controls defined in step 1 into auditing rules. For testers, this is the verification of the implementation of the audit controls. Are they too tight, too loose, inappropriately applied, etc..
  3. Configure Environment – Follow the documentation step-by-step to make sure that it explains how any external rules should/are setup.
  4. Deploy Collection Agents – Another documentation check. This time it is about how you setup the server to collect auditing information
  5. Monitor Execution – This is where the knowledge learned during standards training kicks in and a bulk of the time will likely be spent testing. You need to make sure that the information captured is exactly what you are looking for — and nothing else. Too much information is almost as bad as not enough. Can you as a tester do something that should be audited, but isn’t?
  6. Report and Review – Are the reports that your system generates as per its claim of compliance with Regulation X: accurate, easy to produce, relevant, professional looking?
  7. Refine and Improve – Make sure that the auditing portion is operating in allowed parameters for CPU consumption, file generation etc.. Whether the speed can be increased through query hints, additional indexes, etc. is the concern of the development group. It however is the tester’s responsibility to provide information regarding performance against the backdrop of historical runs