Achieving SOX compliance requirements is the mandatory for all publicly traded companies. But when it comes to most IT teams, SOX compliance can be quite vague and confusing. SOX compliance is not written with technology mandate in mind, but rather a mandate for accounting, legal, and financial reporting. In the SOX Act there’s no reference can be found to anything specific related to IT. It is often said that SOX was “written by lawyers, for lawyers”
In order to address SOX compliance from an IT perspective, the COSO (Committee of Sponsoring Organizations of the Treadway Commission) framework, makes some general references to IT controls, and COBIT (Control Objectives for Information and Related Technology) framework which sets ahead specific IT control objectives (and closely aligned with COSO) are established.
Particularly because of this complexity, third-party tools such as ApexSQL Audit can be helpful in navigating the requirements and offering a solution to implementing of the SOX compliance requirements related to SQL Server
This part of the article will provide information about COBIT 4.1 control objectives supported by ApexSQL Audit with description:
Plan and Organize
- PO2.4 – Integrity management – Procedures to manage and maintain integrity and consistency of data throughout the complete data life cycle. Ensure data integrity and consistency through regular process of validation.
ApexSQL Audit
Since SOX compliance regulations don’t address IT requirements directly, ApexSQL has created the SOX compliance checklist for ApexSQL Audit document, based on COSO and COBIT framework directives, that aims to facilitate and to show precisely the application areas where ApexSQL Audit can help. The following will describe what requirements have to be addressed by an ApexSQL Audit user and how to set the ApexSQL Audit so that requirement can be fulfilled including recommended auditing settings to match the SOX compliance requirements
PO2.4 Integrity management
ApexSQL Audit can audit any DML activity including the ability to alert. in real-time. on any INSERT, UPDATE and DELETE activity. ApexSQL Audit features two types of auditing filters; the Simple filter and Advanced filter, both capable of precise auditing filtering
To set this requirements via the Simple filter
Select the SQL Server instance in the left pane for which activities have to be audited and tick the Operations in Simple filter in the right pane
Select databases that have to be audited via Add database
Now select the database that must be audited for the DML changes and expand the Operations’ DML filter node where INSERT, UPDATE and DELETE should be checked
Repeat the same procedure for each database where DML changes are required, and in this way Auditing of any DML changes for specified databases will be ensured
The same can be achieved via the Advanced auditing filter as well
Select the SQL Server instance in the left pane and select the Advanced filter type button in the right pane
Since the advanced filter is based on the logical expressions, only two conditions are required in order to achieve the same filtering criteria achieved with the simple filter, add logical expressions as shown below:
On click , <empty> opens the Object browser dialog where desired databases can be added by ticking the checkboxes.
For the SQL Server instance the appropriate databases will be listed below. Use checkboxes to add the needed databases for auditing and click OK. This condition can be interpreted as: audit events that occur on the selected SQL Server where database name is one of databases listed in the condition. The next condition will be used to determine which database operations will be audited, and in this specific case the DML operations INSERT, UPDATE and DELETE have to be audited. To add the new condition press the 
and select Database operations as condition and click on the data field, expand the DML section and select Delete, Insert and Update operations
The final condition should look like this
Whether the simple or advanced filter is used, ApexSQL Audit real-time alerting functionality will allow informing the person in charge timely on any unexpected data change. While DML operations can be tracked if needed for all tables within the selected database, alerts should be created for the database tables with the sensitive data which are not prone to often changes, where each and every DML manipulation with data for these tables could be the potential issue and have to be checked as soon as possible. To set the alerts for required tables:
First select the Alerts tab and then New from the Manage tab
Select Auditing alert (from the new Alerting wizard dialog)
In the next alert wizard dialog, Alert title and description in this dialog allows specifying the alert title and report body text and these fields are fully customizable. The Limit the number of reports for this alert to one per minute by default (for each server) option is here to prevent an inordinate amount of reports in case of an events number spike. In this particular case where the sensitive data are not prone to often changes and considering their importance, this option should be leaved unchecked
In the next step, in Server deployment dialog select the SQL Server instance by checking the appropriate checkbox
These steps described above are the more or less the common steps and generally self-explainable, so they will not be addressed anymore in the text, but rather just the specific filtering condition required for alerting
The advanced filter is used for defining the alert conditions as well, and in our case here is an example of the filter that will trigger the alerts for DML events that occurs on specified tables in specified databases
In the next Actions wizard step the Send this alert report via email checkbox should be selected in order to get notified when alert occurs via email. These are also the common steps for every alert created, so these steps will not be addressed again in the rest of the article
Clicking on the link Click here to configure an account for sending e-mail allows configuring of the mail account
The final Alert summary dialog, shows all the relevant information about the alert that will be created when press on OK
By configuring auditing and alerting in this manner, ApexSQL Audit will be set to audit and report on any unauthorized, accidental and malicious modifications of data and thus decreasing the risk of data integrity violation, while real-time alerts allow for a quick response when such events occur
The second part of the article describes how to comply with the following COBIT 4.1 control objectives:
- Acquire and Implement
- Delivery and Support
July 23, 2015
