Sharpen Your Basic SQL Server Skills - 28 Mar 2008

Sharpen Your Basic SQL Server Skills - 28 Mar 2008

Surface Area Configuration tools reduce exposure to security risks


Executive Summary:

Get answers to your questions about SQL Server 2005 Surface Area Configuration tools that can help you protect the SQL Server surface area from security breaches perpetrated by unauthorized users or applications.

Q: What’s the SQL Server surface area, and what security risks are associated with it?
A: All SQL Server components that are accessible by users or applications are known as the surface area. Enabling all of SQL Server’s features increases the risk of a security breach by unauthorized users or applications. To make the vulnerabilities of the surface area less visible to potential attackers, the default installation of SQL Server 2005 disables most of the at-risk features (e.g., xp_cmdshell, dedicated administrator connection). You can also deactivate unused components to reduce surface area exposure.

Q: What tools are available to increase the security of the SQL Server surface area?
A: You can use two SQL Server 2005 Surface Area Configuration tools to increase surface area security and manage common service-related operations. These tools use Window Management Instrumentation (WMI) API calls to manage SQL Server’s registry operations. The Surface Area Configuration for Services and Connections tool gives you a simple method for configuring the security of local, as well as remote, SQL Server machines. For more information, see library/ms188980.aspx. With the Surface Area Configuration for Features tool you can enable and disable features of the Database Engine, SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). For more information, see

Q: What features does the Surface Area Configuration for Features tool include?
A: The features in the Surface Area Configuration for Features tool are divided into three main categories: SSAS, Database Engine, and SSRS. SSAS features include Ad Hoc Data Mining queries, Anonymous Connections, Linked Objects, and User-Defined functions. Database Engine features include Ad Hoc Remote Queries, Common Language Runtime (CLR) Integration, Database Mail, OLE Automation, Service Broker, SQL Mail, Web Assistant stored procedures, xp_cmdshell, Dedicated Administrator Connection, and Native XML Web Services. SSRS features include HTTP access and Web Service Requests, Scheduled Events and Report Delivery, and Windows Integrated Security.

Q: What database administrative services does the Surface Area Configuration for Features tool support?
A: The tool provides an interface from which you can start, stop, pause, or resume administrative services. You can also configure whether the startup is manual, automatic, or disabled. In addition to SSAS, SSRS, and SQL Server Integration Services (SSIS), these configuration services are supported:

  • Database Engine
  • Full-Text Search service
  • SQL Server Integration Services (SSIS)
  • MSSQLServerADHelper service
  • Notification Services
  • SQL Server Agent service
  • SQL Server Browser service
  • SQL Server Writer Service

Q: How do I use the Surface Area Configuration for Features tool?
A: You can use the tool’s GUI, T-SQL in the Query Editor, or a command-line utility. To use the GUI go to Start, All Programs, SQL Server 2005, Configuration Tools, SQL Server Surface Area Configuration for Features. You can use the GUI to select a component and configure its features. To use T-SQL in the SQL Server Query Editor you need to enable xp_cmdshell, as the following example shows.

sp_configure ‘show advanced
  options’, 1
  sp_configure ‘xp_cmdshell’, 1

Use the command-line sac utility when you’re configuring multiple SQL Server machines with the same import and export Surface Area Settings. This utility creates a configuration file on the local server; this file is then exported to another server where the system imports it. The sac utility is in the \Program Files Microsoft SQL Server\90\Shared folder.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.