Minimize SQL Server 2008 R2 Surface Area

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps to improve security by providing fewer avenues for potential attacks on a system.

For new installations of SQL Server 2008, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state.

Use SQL Server Configuration Manager to enable, disable, start, or stop services and configure the remote connectivity of your Database Engine.
Use Policy-Based Management to enable and disable optional features.

Using SQL Server Configuration Manager

The SQL Server Configuration Manager is installed with all editions of SQL Server.

To open the SQL Server Configuration Manager

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

To configure a service to start automatically

In SQL Server Configuration Manager, expand SQL Server Services.
In the details pane, right-click one of the SQL Server services, and then click Properties.
In the Properties dialog box, set Start Mode to Automatic.

To configure the Database Engine to accept remote connections

In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for <instancename>.
In the details pane, right-click one of the available protocols, and then click Properties.
Note
The shared memory protocol cannot be enabled for remote connections.
To enable a protocol for remote connections, set the Enabled box to Yes.

Note
The shared memory protocol cannot be enabled for remote connections.

For help with SQL Server Configuration Manager, view the SQL Server Configuration Manager help file, or see SQL Server Books Online. To configure SOAP and Service Broker endpoints, use CREATE ENDPOINT and ALTER ENDPOINT.

Using Policy-Based Management

Policy-Based Management is configured using SQL Server Management Studio. If Management Studio is not installed, run setup and install the client tools. Management Studio is not part of the installation of SQL Server Express. Download Management Studio Express from Microsoft.com. The features of Policy-Based Management are described in SQL Server Books Online.

To open the SQL Server Management Studio

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

To configure Policy-Based Management

In Management Studio, connect to an instance of Database Engine, expand Management, and then expand Policy Management.
Configure Policy-Based Management by using the following three facets:
- Surface Area Configuration
- Surface Area Configuration for Analysis Services
- Surface Area Configuration for Reporting Services
The surface area configuration features that are turned off by default should not be turned on unless they are required for a specific business need.

Recommended Settings:

Recommended settings for the Database Engine
Set all properties of the Surface Area Configuration facet to false.
Recommended settings for Analysis Services
Set all properties of the Surface Area Configuration for Analysis Services facet to false.
Recommended settings for Reporting Services
Use the Surface Area Configuration for Reporting Services to disable any Reporting Services features that you do not need.

Other Resources

How to: Enable or Disable a Server Network Protocol (SQL Server Configuration Manager)

How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)

Understanding Surface Area Configuration

SQL Server Surface Area Configuration 2005

SQL Server 2005

Updated: 17 July 2006

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state.

Use SQL Server Surface Area Configuration to enable, disable, start, or stop the features, services, and remote connectivity of your SQL Server 2005 installations. You can use SQL Server Surface Area Configuration on local and remote servers.

SQL Server Surface Area Configuration uses Window Management Instrumentation (WMI) to view and change server settings. WMI provides a unified way for interfacing with the API calls that manage registry operations that configure SQL Server. For information about configuring permissions related to WMI, see the topic How to: Configure WMI to Show Server Status in SQL Server Tools.

When to Use the Surface Area Configuration Tools

After you install SQL Server 2005 or upgrade to SQL Server 2005, you should run SQL Server Surface Area Configuration to verify which features and services are enabled and running, and to verify which types of connections SQL Server 2005 will accept. After initial configuration, you can use SQL Server Surface Area Configuration to verify or change the state of features, services, and connections.

Launching Surface Area Configuration

SQL Server Surface Area Configuration is available on the SQL Server Start menu:

On the Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click SQL Server Surface Area Configuration.

The first page to appear is the SQL Server Surface Area Configuration start page. On the start page, specify which server you want to configure:

Click the change computer link adjacent to Configure Surface Area for. The default value is localhost. If you previously selected a named server, you would see the server name.
In the Select Computer dialog box, do one of the following:
- To configure SQL Server 2005 on the local computer, click Local computer.
- To configure SQL Server 2005 on another computer, click Remote computer, and then enter the computer name in the text box.
- To configure a failover cluster, click Remote computer, and then enter the failover cluster instance name in the text box.
Click OK.

Using the Surface Area Configuration Tools

After selecting the computer to configure, you can launch two tools:

Use Surface Area Configuration for Services and Connections to enable or disable Windows services and remote connectivity.
For descriptions of the service and connectivity settings and defaults for those settings, see Surface Area Configuration for Services and Connections.
Use Surface Area Configuration for Features to enable and disable features of the Database Engine, Analysis Services, and Reporting Services.
For descriptions of the features and information about default feature settings, see Surface Area Configuration for Features.

sac Command Line Utility

To import and export surface area settings, use the sac command-prompt utility. Using this utility, you can configure the surface area on one computer, and then apply the same settings to other computers.

The easiest way to use the sac utility is to use SQL Server Surface Area Configuration to configure one computer, and then use the sac utility to export the settings of that computer to a file. You can use that file to apply the same settings to SQL Server 2005 components on other computers.

For more information, see sac Utility.

Other Resources

Surface Area Configuration for Features
Surface Area Configuration for Services and Connections

Configure Surface Area Configuration for SQL Server 2008
Written By: Ashish Kumar Mehta -- 4/29/2010 -- read/post comments -- print --

Rating: (not rated yet) Rate

Win a Kindle with 5 SQL Server eBooks

Problem
I have installed a new instance of SQL Server 2008 Analysis Services and I am unable to find the Surface Area Configuration tool that I used to use in SQL Server 2005. How can I manage the Analysis Services features in SQL Server 2008?

Solution
Surface Area Configuration was a great tool which was introduced by Microsoft in SQL Server 2005 to help SQL Server DBA's to quickly turn ON / OFF many of the features which are turned fff by default in the product. However, Microsoft has removed Surface Area Configuration tool in SQL Server 2008. Now the question is how do you manage the Surface Area Configuration for Analysis Services? Database administrator can now use Policy Based Management to enable or disable feature for Analysis Services. Let's go through the steps to enable or disable Analysis Services features using Policy Based Management.

Configure Surface Area Configuration for SQL Server 2008 Analysis Services

1. Connect to SQL Server 2008 Analysis Services Instance using SQL Server Management Studio.

2. In Object Explorer, right click the SQL Server 2008 Analysis Services Instance and select Facets from the drop down list as shown in the below snippet to open up the Facets window.

3. In View Facets window you will be able to see the list of Facet which are available for SQL Server 2008 Analysis Services. You can select any of the Facet Properties and then select the value as True to enable the feature and choose the value as False to disable the feature as shown in the below snippet.

Once you have changed the values click OK to save the Surface Area Configuration changes. It is advised to keep the unwanted features turned off as this helps to protect your Analysis Services instance from potential attacks.

Analysis Services 2008 features which can be managed using Policy Based Management are:

AdHocDataMiningQueriesEnabled: - The Data Mining Extension (DMX) OPENROWSET statement basically supports the use of ad hoc queries using external providers. Enable ad hoc data mining queries only if your applications and scripts use these statements, otherwise it is better to turn off this feature.
AnonymousConnectionsEnabled: - Anonymous connections allow unauthenticated users to establish connections with your Analysis Services instance. Enable anonymous connections only if your applications require unauthenticated users to connect to the Analysis Service instance, otherwise it is better to turn off this feature.
LinkedObjectsLinksFromOtherInstancesEnabled: - Analysis Services can supports linked objects, which link dimensions and measure groups between different instances. Enable linked objects - links from other instances, only if other instances of analysis services link to objects of the current instance.
LinkedObjectsLinksToOtherInstancesEnabled: -Analysis Services supports linked objects, which link dimensions and measure groups between instances. Enable linked objects - links to other instances, only if this Analysis Services instance link to objects on other Analysis Services instances.
ListenOnlyOnLocalConnections: - Enabling remote connections for Analysis Services opens a TCP/IP port on the server. Enable remote connections only if you want to allow connections for remote computers, otherwise it is better to turn off this feature.
UserDefinedFunctionsEnabled: - Analysis Services can load assemblies that contain user defined functions. These functions can be based on the common language runtime (CLR) or can be Component Object Model (COM) objects. CLR based objects can be secured using the CLR security model, but COM objects cannot be secured. Enable loading of COM functions only if your applications require them, otherwise it is better to turn off this feature.

Next Steps