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
It's been proven that most security problems come from within the organization.
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 -- 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:
- 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.
- Securing the data: permissions, encryption, data integrity.
- 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.
- 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.
- Microsoft tools and third-party tools for SOX security analysis and monitoring:
- 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
- Mantra SOX Activity Auditing Solution
- Guardium SQL Guard
- 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.
- 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:
- Description of the security model (Active Directory, domain groups, SQL Server roles, database roles, etc.).
- A procedure to create a new account when a new employee arrives.
- A procedure to delete/disable an account when an employee leaves the company.
- A procedure to ask for permissions -– with a required end date if possible -– especially for special permissions.
- A procedure for password changes (for SQL Server logins). In SQL 2005 this can be automatic like it is with Windows Authentication.
- 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.
- Description of how the applications should encrypt password files when using SQL Server authentication mode or when passwords are hard coded.
- Description of the periodical security checks (automated/manual).
- Documentation for every manual and automated procedure regarding security changes and checks for future verifications and inspections.
- 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.
- Documentation for all the exceptions.
Here is an example of a manual analysis report (partial list):
|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|
|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:
|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
- SQL Server 2005 – Security and Protection
- SQL Server 2005 Security White Papers
- SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
- Security Guidance for SQL Server
- Microsoft SQL Server 2005 Assessment Configuration Pack for Sarbanes-Oxley Act (SOX)
Note: Only if you have System Center Configuration Manager
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