Tip

Sarbanes-Oxley compliance checklist: IT security and SQL audits

Michelle Gutzait, Contributor

Problem:

My company needs to meet Sarbanes-Oxley compliance for all its Information Technology (IT) department layers and products, including SQL Server. We're running SQL Server 2000 and SQL Server 2005 with no security standards. What is the process for meeting SOX compliance? What else should I do in order to secure my SQL Server environment and prepare for security audits? Where should I start looking?

About SOX and security

    Requires Free Membership to View

Sarbanes-Oxley Act compliance has become a major course of action within organizations. Enacted in 2002, the law was a response to a number of major corporate and accounting scandals and it set new and enhanced security standards. Its intention is to close every possible security crack, mainly in financial and accounting data and the database and application layers. SOX also allows an outside audit and security analysis of any data manipulation.

It's been proven that most security problems come from within the organization.

More on SQL Server security & database compliance:

 That said, it is essential to first implement internal security and to make sure the right people have access to the right data. SOX compliance and security best practices impose these three rules, also applied when it comes to securing the data:

Confidentiality-Integrity-Availability

  • Confidentiality -- Protecting sensitive information from unauthorized disclosure or intelligible interception.
  • Integrity -- Safeguarding the accuracy and completeness of information and software.
  • Availability -- Ensuring that information solutions are available when required.
  • SQL Server 2005 inclusively addresses those three database security concepts.

Security practices for a database environment

Securing the SQL database server and databases includes maintaining the following:

    1. Security model and permissions: the integration of the database server security model with the OS environment, such as domain, local users, groups and so on.
    2. Securing the data: permissions, encryption, data integrity.
    3. Auditing: being able to perform an analysis on connected users and, in some cases, the ability to discover the who, what and when information on financial databases and data modifications.
    4. Processes and documentation: have security procedures in place and, as a best practice, also documented.

Security analysis and monitoring – possible solutions

To attain SOX compliance, here are the two main solutions: (1) Use Microsoft tools or third-party tools and (2) perform manual analysis and monitoring.

Description

    1. Microsoft tools and third-party tools for SOX security analysis and monitoring:
    2.  

      • Microsoft Security Baseline Analyzer, a free tool that provides basic security analysis of a machine (possibly a remote machine) and installed Microsoft software and SQL Server instances and databases. Download the Security Baseline Analyzer here.

        Figure 1: Example of security update scan results found in Microsoft Security Baseline Analyzer. (Click on image for enlarged view.)

         

      • Mantra SOX Activity Auditing Solution
      • Guardium SQL Guard

       

  1. Manual analysis:

    If you're planning to do a manual security analysis, I recommend you first run the Microsoft Baseline Analyzer described above.

    As a second step, you would probably need to analyze your server and database according to SQL Server 2000 SP3 Security Features and Best Practices. The title implies SQL 2000 SP3, but it is the same for SQL 2005. Refer to the Manual Analysis – example section to view an analysis done on SQL 2005 using this list.

Procedures and documentation

SOX compliance with security procedures and documentation for the SQL Server should include the following:

    1. Description of the security model (Active Directory, domain groups, SQL Server roles, database roles, etc.).
    2. A procedure to create a new account when a new employee arrives.
    3. A procedure to delete/disable an account when an employee leaves the company.
    4. A procedure to ask for permissions -– with a required end date if possible -– especially for special permissions.
    5. A procedure for password changes (for SQL Server logins). In SQL 2005 this can be automatic like it is with Windows Authentication.
    6. Rules and regulations to follow for new software or a new application using a SQL Server database. You should also apply them to existing applications as much as possible.
    7. Description of how the applications should encrypt password files when using SQL Server authentication mode or when passwords are hard coded.
    8. Description of the periodical security checks (automated/manual).
    9. Documentation for every manual and automated procedure regarding security changes and checks for future verifications and inspections.
    10. A procedure for sending notifications regarding security issues (automated/manual) and a procedure for fixing the issues -- who is responsible for what and so on.
    11. Documentation for all the exceptions.

Here is an example of a manual analysis report (partial list):

Administrator checklist

Security check Actions to take Status (OK, missing, other)
Physical security Ensure the physical security of your server. OK
Firewalls Put a firewall between your server and the Internet. OK
  Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too. TCP 1433 blocked; 1434 UDP should be blocked.
  In a multi-tier environment, use multiple firewalls to create screened subnets. OK
Isolation of services Isolate services to reduce the risk that a compromised service could be used to compromise others. OK
  Never install SQL Server on a domain controller. OK
  Run separate SQL Server services under separate Windows accounts. OK;
It was decided to use the same login for SQL Server and SQL Server Agent service account due to application requirement.
  In a multi-tier environment, run Web logic and business logic on separate computers. OK
Service accounts Create Windows accounts with the lowest possible privileges for running SQL Server services. OK;
It is required to have the SQL Server service account as a local administrator.
File System Use NTFS. OK
  Use RAID for critical data files. OK

Developer checklist

Security check Actions to take Status (OK, missing, other)
Effectiveness of ownership chaining Use ownership chaining within a single database to simplify permissions management. OK
  Avoid using cross database ownership chaining when possible. OK
  If you must use cross database ownership chaining, ensure that the two databases are always deployed as a single administrative unit. N/A
Roles that simplify permission management and ownership Assign permissions to roles rather than directly to users. OK;
Except for DB1029 database. This issue is currently analyzed by the development team.
  Objects may be owned by roles, rather than directly by users, if you want to avoid application changes when the owning user is dropped (SQL 2000). OK
Encryption (SSL or IPsec) Enable encrypted connections to your server and consider allowing only encrypted connections. OK
  When allowing SQL Server Authentication, you are strongly urged to encrypt either the network layer with IPsec or the session with SSL. OK
SQL Server errors Your application should not return SQL Server errors to the end user. Log them instead, or transmit them to the systems administrator. OK;
Except for the following applications:
- App1
- App2
- App3
SQL injection Defend against SQL injection by validating all user input before transmitting it to the server. OK
  Limit the scope of possible damage by permitting only minimally privileged accounts to send user input to the server. Most applications need to use a dbo user.
In critical databases DB8728 and DB893, data modification is audited.
  Run SQL Server itself with the least necessary privileges. Not possible due to application requirements.

Related SQL Server database security and SOX compliance links

Note: Only if you have System Center Configuration Manager

Conclusion

Keeping your SQL Server environment secured is one of the most important, if not the most important rule in a database environment. Even though you don't have an official SOX compliance inspection, you should always make sure your data and databases are secured.

ABOUT THE AUTHOR
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server.

This was first published in May 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.