The first part of the article provides a common application settings information and described how to meet the COBIT 4.1’s PO2.4 – Integrity management control objective requirements.
This sequel will describe recommended settings of the ApexSQL Audit that helps meeting the COBIT 4.1 control objectives:
Acquire and Implement
- AI3.2 – Control movement of data by ensuring that this is performed by an independent and authorized group. Audit and log access to sensitive infrastructure components, and ensure regular review
- AI3.3 – Review regularly the vulnerability to unsupported infrastructure by taking in consideration future risks, including security vulnerabilities
- AI6.1 – Establish and maintain control of all version changes. Establish appropriate audit trails to record the most important steps in the change management process. Ensure timely closure of changes. Report on all changes that are not closed on time
- AI6.3 – All emergency access preparations for changes must be appropriately authorized, documented and annulled after the change has been applied. Establish auditing and review of all emergency changes logs
- AI6.4 – Make sure to generate change status reports to enable management review and monitoring of both the detailed status of changes and the overall state. Ensure reports form an audit trail to ensure that changes can be tracked subsequently from beginning to potential disposition. Audit changes to ensure that all approved changes are closed timely in accordance with priority
- AI7 – Evaluating and approving test results by business management. Performing post-implementation reviews Document the final acceptance testing results, including the audit log and present it in a form of reports that are understandable to management and IT stuff to ensure that an informed analysis and assessment can take place
Delivery and Support
- DS4.3 – Ensure resilience, response and recovery requirements by tracking all security driven events to register all security violations and register all unplanned or unwanted changes
- DS5 – Establish regulations over the SQL Server process of security that satisfies the business requirement of preserving the data and structure integrity and minimize the effect of security weaknesses and incidents
- DS9 – Ensuring the SQL Server integrity and complete configurations. Establish the maintenance of an accurate and complete configuration. Process includes auditing of initial configuration, verifying and auditing further configuration
- DS10.2 – Identification and classification of problems based on auditing, including the information needed for analysis of reported problems
- DS11.2 – Establish procedures for effective and efficient track of SQL Server data, retention and archiving to match defined business objectives, the security policy and regulatory requirements
- DS11.5 – Define and implement procedures for auditing and tracking backup and restoration of SQL Server data to fulfill the defined business requirements and the continuity plan
- DS11.6 – Establish SQL Server auditing to identify and track applying of security requirements, sensitive information processing and output of data to comply to business objectives, the organization’s security policy and regulatory requirements
- DS13.3 – Establish SQL Server auditing to ensure that sufficient chronological audit information is being collected and stored in logs to make possible the reconstruction, review and examination of the operations time sequences and the other activities surrounding or supporting operations
AI3.2 Infrastructure resource protection and availability
This requirement is explained in the article How to ensure continuous auditing of SQL Server with zero audited data loss.
AI6 Change standards and procedures (AI6.1, AI6.3, AI6.4, AI6.5), AI7 Install, Accredit Solutions and Changes and DS13.3 IT Infrastructure Monitoring
This requirement is generally related to reporting ability of ApexSQL Audit. ApexSQL Audit can be set to audit and collect any event that occurs on SQL Server, and this allows for comprehensive reporting on collected events with all required information needed
ApexSQL Audit can report on any collected event and the following image shows the information columns that are available for the collected SQL Server events
For reporting purpose, common or custom reports can be used. More details in the ApexSQL Audit Feature highlight: Custom reports article
AI3.3 Infrastructure maintenance, DS4.3 Critical IT resources, DS5 Ensure Systems Security and DS11.6 Security requirements for data management
This requirement is focused on ensuring the necessary auditing of all security related changes on a server and/or database level. The best practice for DS 4.3 is to include reviewing stored procedures and DDL/DML triggers for security concerns. Stored procedures and triggers are based on procedural programing language making them easy targets to hide malicious code
To set the simple auditing filter
Server level auditing filter
On the database level the following filter settings are recommended
DML settings can be additionally narrowed by selecting only the required tables for DML auditing via include/exclude objects filter
Select the Objects will open the Add objects to filter in the right pane that contain data important from the auditing point of view
Select the tables that should be audited in the left pane and add them for auditing by ticking the checkboxes. After this filter is set, only the tables listed in the Include objects pane will be audited. To preview the set up for both objects and operation click 
button.
The same have to be done for each database added for auditing, although, some auditing requirements might be different for different databases
Top achieve the same auditing functionality via the advanced auditing filter, the following auditing condition should be set
In the specific example, only the specified tables from the AdventureWorks2014 table are chosen for auditing of DML events, but this can be set according to users’ specific requirements and depending on the way of SOX compliance implementation
DS9 Manage the Configuration
ApexSQL Audit can help users to meet the DS9.1, DS9.2 and DS9.3 requirements, as it utilize a tamper evident central repository database. To set and configure the Central repository database tampering alert, select Alerts in the application left pane and then the Manage tab in the right pane.
The Central repository database tampering alert will be listed as enabled alert by default.
This will activate Central repository database tampering alert. Now, alert can be edited according to the preferences or predefined strategy. To edit the alert use the Edit button from the Manage alerts menu or simply double-click on the alert name
Press Next in this dialog if no editing is required
In the Check interval dialog it is possible to set the starting date and time for verifying the repository database integrity and time period on which the verify integrity operation will be executed
It is strongly recommended to enable the email notification for the repository database tampering alert.
Besides being able to alert and track the data tampering, ApexSQL Audit can self-track all auditing configuration changes, which is the standard requirement by auditors, since they must ensure that auditing report they are reviewing is fully complete and that there are no missing parts due to temporarily malicious or accidental change of auditing settings. The example below shows how the auditing settings were maliciously changed for a short period of time to prevent the auditing of the UPDATE operations
As it can be seen from the Audit settings history report the user Administrator changed the auditing settings temporarily by excluding the Update events for the adw2012 database from auditing at 02:44:35 and after ~30 seconds he added the Update filter for the adw2012 database again. When reviewing the daily report, the person in charge for SQX compliance implementation and maintenance can detect the unplanned auditing settings change and react accordingly with enough information to track when and who performed the change of the auditing settings
DS10.2 Problem tracking and resolution
ApexSQL Audit is can audit and collect all SQL Server events on the server and database level. With custom reporting, which ensures the high level of reporting precision, ApexSQL Audit is capable of analyzing and investigating potential issues and precisely locating the source. Custom reports ensure not only creating the conditions that will exclude all unnecessary data and focus attention only on the very narrow set of events needed for analyses of the issue, but an embedded T-SQL parser allows forensic reporting by filtering the specific text in the T-SQL of executed events which allows utmost reporting precision allowing quick and easy locating the root causes of the SOX compliance issues
The image below shows the simple example of how the locating the malicious UPDATE operation can be narrowed down by using the text data to narrow down the report only to the events that are aiming at the sensitive data columns
More on custom reports can be found in ApexSQL Audit Feature highlight: Custom reports article
DS11.2 Storage and retention arrangements and DS11.5 Backup and restoration
ApexSQL Audit can audit all backup and restore operations. It has an additional ability to alert on any such event and to create reports that will allow insight in the full backup chain to ensure that the backup policy is properly maintained and conducted. It also can report on all restore events. The report contains who, when and what along with information of successful/unsuccessful backup/restore attempts
To ensure auditing of all backup and restore server level events, enable the following in the simple auditing filter
After selecting the Backup\restore checkbox, all backup and restore operations for every database in the selected SQL Server instance will be audited
The Advanced filter at the other hand allows more granular auditing for specific databases
To create the filtering criteria that will audit all databases for the selected SQL Server instance
When database and backup events have to be audited only for specific databases, the following advanced filter criteria can be used
In this specific case the backup and restore operations will be collected only for the AdventureWorks2014, bagetyv2, DefaultDb1 and QBase databases listed in the Database name condition
Below is an example of how ApexSQL Audit reports on the backup operations of the AdventureWorks2014 database. From Text data field in report it is easily distinguishable when a full and when the differential backup is performed including the precise backup time. From report like this it can be easily recognized when backup operations is not executed in scheduled time
To be sure that backup is performed in the scheduled time, it is possible to create the informative Low alert that will be triggered every time when backup is performed informing the administrator about the proper backup policy maintaining
To set the informative backup alert
Make sure that severity is set to Low, which mean that this is the informative alert and that Limit checkbox is unchecked to ensure that alert will be triggered and info sent for every backup event. In the Conditions dialog of the Create new alert wizard, set the following condition
Enable the email alert to be sent when alert is triggered, and email should be sent every time when a backup operation is scheduled
July 28, 2015
