MS SQL Server audit: Surface area reduction (part 1)

SQL Server has a number of components that allow clients to connect and communicate with it. Microsoft introduced the term, “Surface Area Reduction” as a security measure that involves stopping or disabling unused components. Like the name suggests, it reduces the number of ways that an attacker could try to interrogate the SQL Server.

This post discusses the following:

• Surface area reduction using Microsoft SQL server-wide configuration (sys.configurations) using the Center for Internet Security (CIS) SQL Server guidelines as a benchmark
• SQL Server Policy to reduce surface area configuration
• Which Services should be disabled
• Surface area reduction using other tables and views using Center for Internet Security (CIS) SQL Server guidelines as a benchmark

Note: The queries mentioned in this article are based on SQL Server 2008 R2. They all should be applicable to SQL Server 2012. Older versions of SQL Server may have different syntax.

Server-wide configuration

Microsoft SQL Server has amongst all of its features, catalog views that return information used by its database engine. One particular catalog information that provides details about all the server wide configuration values is “sys.configurations”. Such information covered include: audit modes, language settings, memory usage, tracing and type of stored procedures enabled.

The following SQL query displays all the server-wide configuration values:

SELECT name, cast(value_in_use as char) FROM sys.configurations;

In general, to reduce the surface area, you should disable as many unnecessary features and services as possible without affecting the performance and functionality of your SQL Server Database Engine. Furthermore, some audit features should be enabled to be log access yo your SQL Server Database Engine. Fortunately, the Center for Internet Security provide security benchmarks for SQL Server that can be followed to cover off disabling unnecessary features.

CIS have written security benchmark guides for SQL Server 2005, 2008 and 2012. It is noted that the SQL Server 2008R2 and 2012 are very similar in layout and recommendation.

Based on the CIS security benchmarks recommended for SQL Server 2008R2 and 2012, for surface area reduction, the following configuration settings relating to the server-wide configuration (sys.configurations) should be disabled for compliance.

1. Ad Hoc Distributed Queries
2. CLR Enabled
3. Cross DB Ownership Chaining
4. Database Mail XPs
5. OLE Automation Procedures
6. Remote Access
7. Remote Admin Connections
8. Scan For Startup Procs
9. SQL Mail XPs

Note: the last configuration setting, “SQL Mail XPs” is not covered in the CIS Microsoft SQL Server 2012 Database as that setting was deprecated in that version with “Database Mail XPs” as its replacement.

Other configuration settings of interest:

• Agent XPs – Extended Stored Procedures for SQL Server Agent should be disabled. Default value is ’0′ (disabled)
• C2 Audit Mode – Logs both successful and successful attempts to access statements and objects (superceded by Common Criteria Compliance). C2 Audit Mode should be enabled
• Common Criteria Compliance Enabled – Replacement for ‘C2 Audit Mode’ (only available for Enterprise/Developer versions). Common Criteria Compliance should be enabled
• SMO and DMO XPs – Extended Stored Procedures for SQL Server Management Objects (SMO) and Database Management Objects (DMO) ideally should be disabled. However, Default value is ’1′ (enabled). If disabled, SQL Server Management Studio will not see the database instance and to load the database instance force this setting to be re-enabled
• Default Trace Enabled – Tracing should be enabled. Default value is ’1′ (enabled)

The remediation covered by the CIS security benchmark is to show advanced options and then configure the configuration setting, with 0 (for disable) as appropriate.

For example to disable “Scan For Startup Procedures”:

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Scan for startup procs', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

SQL Server policy

Microsoft recommend that policy-based management for SQL Server is used. In earlier versions of SQL Server, this was covered using Surface Area Configuration. This was removed from SQL Server 2008 and its functionality was moved into policy management facets in SQL Server Management Studio.

Using SQL Server Management Studio, within the Object Explorer, navigate to the SQL Server Instance and expand the path following:

Management\Policy Management\Facets

The following facets should be disabled (disabled by default) according to Microsoft:

• Surface Area Configuration
• Surface Area Configuration for Analysis Services
• Surface Area Configuration for Reporting Services

As seen in the following screenshot example:

Services

Microsoft also recommend turning off any unneeded services by setting the service to either manual startup or disabled using SQL Server Configuration Manager.

Microsoft do not specify exactly which services that should be disabled. Obviously, you want the core SQL Server service but you could in theory run without the other services. Double check if you need other services such SQL Server analysing and SQL Server reporting services.

To see what services are currently running or disabled, in SQL Server Configuration Manager:

1. Click on SQL Server Services
2. In Details Pane, right-click on a service and choose properties
3. To change the start mode, select “service” tab and edit the start mode entry

As seen in the following screenshot example:

Summary

In this post, we have had a look at reducing surface area by:

• Modifying the server-wide configuration (sys.configurations view)
• Applying SQL Server policies
• Turning off unneeded SQL services

In the second part of the article, we will look at other features and SQL queries that can be used to reduce the surface area and comply with good security practices.