How to implement SOX compliance requirements for SQL Server – Part 1

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 DML activities have to be tracked and select the Simple filter type radio button 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 radio 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

Click on Press here to add condition and then click on the data field to select Database name

Clicking on the will open the Object browser dialog where desired databases can be added using the Insert button

Here from the Server drop down menu select the SQL Server instance first and then the appropriate databases will be listed below. Use the Insert button to add the needed databases for auditing and close the dialog. 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 Data alert (from the drop down menu

Selecting the Data alert option

In the Create new alert wizard, Alert name and notification options dialog allows specifying the alert name, subject and report body text and these fields are fully customizable. The Limit the number of reports for this alert to one per minute (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 Account settings dialog, shown upon clicking the Click here to configure as account for sending e-mail link

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