SOX survival kit for the SQL Server DBA

The Sarbanes–Oxley Act of 2002, Sarbanes–Oxley, Sarbox, or SOX is a US federal law “written by lawyers for lawyers”. It’s a regulation created to improve the quality and integrity of financial reporting, and ensure the financial and business information is factual and accurate.

Why was SOX created?

Remember the Enron scandal? What about Tyco, Worldcom, Adelphia?

In the nineties, Enron was one of the biggest and financially healthiest US companies. It was one of the world’s largest paper, gas, oil, electricity, and communications companies before, to everyone’s surprise, it went bankrupt in 2001.

Why was the bankrupt such a surprise? Enron showed inaccurate financial and earnings reports to its investors, shareholders and employees, encouraged them to invest in Enron stocks, and embezzled company money. The reports Enron management presented showed positive earnings, so the investors were misled to continue investing. After Enron bankrupted, the investors lost the money, and many Enron employees who invested in Enron stocks lost entire retirement portfolios.

To prevent corporate fraud, protect investors and shareholders, improve the accuracy and reliability of corporate disclosures such as earnings reports, Senator Paul Sarbanes and Representative Michael Oxley created the Sarbanes-Oxley Act. The CEO and CFO are made responsible for the accuracy of all financial reports. Any misrepresentations, material changes and potential fraud must be reported, investigated, prevented and fixed by company’s internal accounting controls, before reaching public.

The act was approved by the United States House of Representatives, by the Senate and signed by President George W. Bush on July 30, 2002.

How does SOX affect your databases?

SOX is very vague in specifying IT department and database requirements. However, from the section 404, it’s clear that it requires data accuracy.

“SEC. 404. MANAGEMENT ASSESSMENT OF INTERNAL CONTROLS.
(a) RULES REQUIRED.—The Commission shall prescribe rules requiring each annual report required by section 13(a) or 15(d) of the Securities Exchange Act of 1934 (15 U.S.C. 78m or 78o(d)) to contain an internal control report, which shall –
(1) state the responsibility of management for establishing and maintaining an adequate internal control structure and procedures for financial reporting; and
(2) contain an assessment, as of the end of the most recent fiscal year of the issuer, of the effectiveness of the internal control structure and procedures of the issuer for financial reporting.
(b) INTERNAL CONTROL EVALUATION AND REPORTING.—With respect to the internal control assessment required by subsection (a), each registered public accounting firm that prepares or issues the audit report for the issuer shall attest to, and report on, the assessment made by the management of the issuer. An attestation made under this subsection shall be made in accordance with standards for attestation engagements issued or adopted by the Board. Any such attestation shall not be the subject of a separate engagement.”¹

As the only way to provide accurate data is to ensure a secure and controlled environment, we’ll focus in that.

How to prepare for SOX compliance?

A general recommendation for compliance with SOX is to secure SQL Server that hosts financial reporting and accounting systems, and audit the events.

In order to efficiently set SQL Server security, it’s recommended to apply the following security actions before running a SQL Server setup:

• Apply all Windows service packs and updates
• Use antivirus programs, malware protections, firewalls, and other protection tools
• Create a dedicated account under which SQL Server service will run and make sure it only has the necessary privileges. Do not use an account with admin privileges to run SQL Server service
• Grant access to the host only to the privileged users
• For web applications, use Secure Sockets Layer (SSL), Transport Layer Protection (TLS) or other network encryption technology

SQL Server instance security recommendations

• Define roles and responsibilities among DBAs, to prevent unauthorized changes that can lead to security issues and compliance failure
• Don’t install unnecessary SQL Server features (if they have already been installed, disable them)
• Apply all SQL Server service packs and updates
• Use Windows authentication, if possible
• Change the default SQL Server ports (1433 and 1434)
• Allow access to the SQL Server installation files, folders and database files only to the account used to run SQL Server service
• Keep only production databases on the production server. Use other servers for testing, QA, development
• Monitor successful and failed logins. This will not prevent attackers, but will help you identify security gaps
• Disable remote logins, unless necessary
• Use Transparent Data Encryption (TDE)

SQL login and user recommendations

• Grant each user minimal permissions necessary for his/her job description – you don’t want your HR officer have any insight into sensitive accounting data, or even worse, be able to change it
• Enforce a strong password policy – using passwords such as 123 or your birth date will not keep your data secure. Strong passwords are complex passwords that include length and character requirements and make passwords more difficult for attackers to crack
• Disable or rename the “sa” login – the existence of a known username increases the probability of password cracking in a brute-force attack
• Remove unused logins – not just hackers can be a threat. You don’t want an ex-employee to have any access
• Remove/disable the guest account – the guest account is created by default. Although it’s not granted access to any databases, it still represents a threat as it can be misused by hackers
• Disable anonymous logins – if not, an attacker will be able to connect to the SQL Server instance anonymously, without providing any authentication credentials
• Don’t grant any permissions to the public role – by default, the public role is granted no privileges on databases. It’s recommended to keep it that way

To enforce all login and user recommendations, you must GRANT, DENY or REVOKE permissions to your users.

To enable a user do something on a specific database or database object, you must grant him/her specific privileges. You can do that in SQL Server Management Studio, the Permissions tab for database or object properties.

Or by using T-SQL

USE AdventureWorks2012;
GRANT INSERT, UPDATE ON HumanResources.EmployeeDepartmentHistory TO JohnSmith;
GO

To restrict permissions, use DENY

USE AdventureWorks2012;
DENY INSERT, UPDATE ON HumanResources.EmployeeDepartmentHistory TO JohnSmith;
GO

The REVOKE statement rolls back the last permission action (GRANT or DENY), so the user permission state can be granted or denied, depending on the user history. To be sure of the user permissions, avoid REVOKE

How to audit a database?

After setting SQL Server security, it’s highly recommended to audit events on SQL Server, to make sure the protection is set correctly, the system is running as expected and no unwanted access or changes are happening.

Before setting up auditing, check the following database auditing recommendations:

• Audit events on all SQL Server instances, databases and database objects
• Make sure all security changes and access/activity on tables that store sensitive data are audited
• Auditing should not affect user activity or system performance
• Reporting is easy and flexible
• Audited records can be archived and stored for a specified time period, without affecting the live system
• Reporting using archived data is possible and seamless
• Auditing is not affected by schema/organization changes
• Auditing can be easily customized
• Auditing is real-time or almost real-time, so the audited records can be accessed almost immediately after the event happened

This is where ApexSQL Audit can help:

ApexSQL Audit is a SQL Server auditing and compliance tool that provides a variety of options for auditing SQL Server events, ensures compliance with policies, and monitors SQL Server security by tracking permission, login and password changes. It has a range of built-in reports, commonly needed for compliance auditing. For more specific requests, custom reports can be created using a drag-and-drop technique.

To set up auditing using ApexSQL Audit:

1. Start ApexSQL Audit
2. Select the SQL Server instances you want to audit
3. For each instance, specify whether to audit data changes and security events
4. Select the databases, operation types and tables you want to audit
5. In the notification that pops up, click Apply to apply the new settings

   

All selected events will be audited and the captured information will be stored in the central repository. ApexSQL Audit provides many built-in reports that provide information that helps meeting SOX compliance requests

For example, if someone tries to login to a SQL Server instance using a non-existing login

This event will be classified as an unauthorized access attempt and will be shown in the Unauthorized access report. As it represents a potential threat, all these events must be investigated

Any modification of user permissions is shown in the Permission changes report

However, if this report doesn’t provide sufficient filtering (as it shows history for all SQL Server instance users on all databases), you can create a custom report that will show the changes only for a specific user on a specific database

If records were inserted, updated or deleted from a table, the DML history report will show the data changes

To survive SOX, you must create a safe environment and keep your SQL Server instances and databases secured. To make sure no security changes are made and no threats exist in your system, use ApexSQL Audit to audit events on SQL Server instances, databases and objects

¹ U.S. Securities and Exchange Commission A White Paper Proposing Practical, Cost Effective Compliance Strategies

Useful resources:
An Act to protect investors by improving the accuracy and reliability of corporate disclosures made pursuant to the securities laws, and for other purposes