Surface Area Configuration Using Policy Based Management

The SQL Server Surface Area Configuration Tool (SQLSAC) was a utility introduced by Microsoft in SQL Server 2005 to help SQL Server Database Administrators or Developers manually turn on features which are off by default in the product. The previous article titled Using the SQL Server 2005 Surface Area Configuration Tool explains how database administrators or developers can use SQL Server Surface Area Configuration tool. 

The SQL Server Surface Area Configuration (SQLSAC) tool has been removed in SQL Server 2008. However, administrators can continue to manage all the SQL Server Services, SQL Server Network Configurations and SQL Native Client 10.0 Configurations using SQL Server Configuration Manager (SQLServerManager10.msc).

Access SQL Server Configuration Manger in the below mentioned ways:-

1. Start | Run | Type “SQLServerManager10.msc”
2. Start | All Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager

As SQL Server Surface Area Configuration Tool (SQLSAC) is not available in SQL Server 2008, how are features enabled that are by default disabled for Database Engine, Analysis Services and Reporting Services? SQL Server 2008 permits management of Surface Area Configurations for the Database Engine, Analysis Service and Reporting Services using Policy Based Management. A new Facet, Surface Area Configuration, is available in SQL Server Management Studio for use by database administrators to enable or disable features of Database Engine, Analysis Services and Reporting Services.

Introduction to Policy Based Management
Policy Based Management or Declarative Management Framework (DMF) is a new feature introduced in SQL Server 2008. Database Administrators have previously encountered difficulty ensuring that all SQL Servers are configured according to the policies and standards of the organisation. Using Policy Based Management feature Production DBA’s can now manage one or more instances of SQL Server 2008 for any policy compliance or non compliance issues. The policies can be created to manage all the entities of SQL Server 2008, such as SQL Server Configurations, Databases, Tables, Users, Roles or any other SQL Server objects etc. The previous article titled Configure and Manage Policy Based Management in SQL Server 2008 discussed how to configure and manage policy based management in SQL Server 2008.

Configure Surface Area Configuration for the Database Engine using Policy Based Management
Configure Surface Area Configuration for the Database Engine in SQL Server 2008 by right clicking SQL Server instance name and then choosing the Facets option from the drop down list as shown below.

 

This opens the View Facets window, select Surface Area Configuration from the Facet drop down list. Once selected Surface Area Configuration, the Facet properties can be seen as shown in the below snippet. 

 

Below mentioned are the features related to Database Engine which can be enabled or disable using Policy Based Management.

1) AdHocRemoteQueryEnabled
2) ClrIntegrationEnabled
3) DatabaseMailEnabled
4) OleAutomationEnabled
5) RemoteDacEnabled
6) ServiceBrokerEndPointEnabled
7) SoapEndPointsEnabled
8) SQLMailEnabled
9) WebAssistantEnabled
10) XPCmdShellEnabled

Under Facet properties choose the feature to enable and then, select the value as “True” from the corresponding drop down list. It is recommended to disable unused features, this helps to keep the SQL Server safe from potential attacks. Once the appropriate changes are done then, click OK to save changes to the server.

Enable any of the above mentioned Database Engine features using sp_configure system stored procedure. The below mentioned is the sample code which can be used to enable 'Ad Hoc Distributed Queries' feature of Database Engine.

USE master
GO
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

Configure Surface Area Configuration for the Analysis Services using Policy Based Management
Configure Surface Area Configuration for the Analysis Services in SQL Server 2008 by right clicking Analysis Service instance name and then select the Facets option from the drop down list as shown in the below snippet.

 

This will open the View Facets window, select Surface Area Configuration for Analysis Services from the Facet drop down list. Once selected Surface Area Configuration for Analysis Services Facet, the Facet properties can be seen as shown in the below snippet. 


 
Below are features related to Analysis Services which can be enabled or disable using Policy Based Management.

1) AdHocDataMiningQueriesEnabled
2) AnonymousConnectionsEnabled
3) LinkedObjectsLinksFromOtherInstancesEnabled
4) LinkedObjectsLinksToOtherInstancesEnabled
5) ListenOnlyOnLocalConnections
6) UserDefinedFunctionsEnabled

Under Facet properties select the feature to enable, then select the value as “True” from the corresponding drop down list. It is recommended to disable unused features, this keeps the Analysis Services Instance safe from potential attacks. Once the appropriate changes are done then, click OK to save changes to the server.

Configure Surface Area Configuration for the Reporting Services using Policy Based Management
Configure Surface Area Configuration for the Reporting Services in SQL Server 2008 by right clicking Reporting Service instance name and then by choosing Facets option from the drop down list as shown in the below snippet.

 

This opens the View Facets window, select Surface Area Configuration for Reporting Services from the Facet drop down list. Once selected, Surface Area Configuration for Reporting Services Facet the Facet properties will be seen as shown in the below snippet. 

 

Below are features related to Reporting Services which can be enabled or disable using Policy Based Management.

1) ReportManagerEnabled
2) ScheduledEventsAndReportDeliveryEnabled
3) WebServicesAndHTTPAccessEnabled

Under Facet properties select the feature to enable, then select the value as “True” from the corresponding drop down list. It is recommended to disable unused features this way; this keeps the Reporting Services Instance safe from potential attacks. Once the appropriate changes are done then, click OK to save changes to the server.

Conclusion
This article demonstrates how database administrators or developers can quickly configure Surface Area Configuration for Database Engine, Analysis Services and Reporting Services with the help of Policy Based Management Feature of SQL Server 2008. It is recommended to disable unused features this way to keep the SQL Server safe from potential attacks.