Secure computing meets SQL Server
As part of Microsoft's secure computing initiative, many new security features were added to SQL Server 2005. While features like encryption and granular permissions control secure databases within a server, other features are dedicated to securing the "surface area" of the server. The surface area includes everything the network can see up to the time login credentials are sent, such as TCP ports, HTTP endpoints and other network-facing services.
Microsoft locked down these services in a fairly straightforward manner. Most are simply off by default. In a fresh installation of SQL Server 2005, many features are disabled until an administrator manually turns them back on. And while this is intended to save less savvy users from unintentionally creating hacker playgrounds, managing all of these services could have turned into a nightmare.
The Surface Area Configuration tool
Luckily, Microsoft thwarted the worst-case management scenario. Shipping with SQL Server 2005 is a simple tool with only one purpose: to manage SQL Server's surface area. Now a DBA has only one place to go any time he must enable or disable an externally facing feature.
You'll find the aptly named SQL Server Surface Area Configuration tool in the Configuration Tools subfolder of the Microsoft SQL Server 2005 Start Menu programs. Like many of the new SQL Server tools, the first thing you'll notice upon starting the Surface Area Configuration tool is that it has a very simple, straightforward interface. As a matter of fact, it presents only two options on the first screen: You can configure "Services and Connections" or "Features."
Figure 1: Straightforward Surface Area Configuration tool interface
Managing Services and Connections
Clicking on the Services and Connections option brings up a dialog with a list of all of the SQL Server-related services running on the server, such as the Database Engine service and SQL Server Agent service. This list even includes non-database engine services such as Notification Services and Integration Services. You will also find instances of SQL Server Express keeping with the goal of the tool -- managing all externally facing services. The tool even finds instances of SQL Server that it wasn't installed with.
Figure 2: Managing Services and Connections
Once a user selects a service from the list, the options are to start, stop, pause or resume the service, in addition to an option to change the startup types -- Automatic, Manual or Disabled. Keep in mind that this tool is not the right place to change startup parameters for the service. To do that, you'll have to bring up SQL Server's Configuration Manager tool, which is geared more toward configuring services than making sure they're secure.
DBAs will definitely want to familiarize themselves with the "Remote Connections"
options available on some of the services, including Database Engine and Analysis Services. These options, turned off by default in many cases, allow remote computers to connect to the local SQL Server instance. If you have problems connecting to a SQL Server and you've already ensured that the service is up and running, this part of the Surface Area Configuration tool is probably the next place you should look.
Figure 3: Remote Connections
Managing features
Clicking on the "Configuration for Features" option brings up a wider assortment of areas to configure. Within the dialog you can enable or disable such SQL Server features as CLR integration, XML Web services endpoints and the DAC (dedicated administrator connection).
Figure 4: Configuration for Features
The more important of these options are:
- CLR Integration: Modifying this option will allow you to enable or disable SQLCLR routines for the entire SQL Server instance. If you're not quite sure whether you trust this feature, this is the place to go to make sure no one can exploit it.
- DAC: The dedicated administrator connection is a special connection that can be used by DBAs in case of extreme server resource depletion. Even in cases when other users cannot connect because there aren't enough resources, SQL Server will attempt to free resources for the DAC. Use this option to enable or disable the DAC for remote access.
- OLE Automation: Many SQL Server developers have had to implement workarounds for the lack of features, such as regular expressions in SQL Server. The easiest way to do so in the past was to use the sp_OA* stored procedures. However, with the introduction of CLR integration, these stored procedures are no longer necessary. If you still need them for backward compatibility, you can re-enable them here.
- xp_cmdshell: Much like the OLE automation stored procedures, xp_cmdshell provided a great workaround for SQL Server limitations in previous versions. However, it's really not recommended for use in production systems because of security implications when opening a command shell. If you need this for legacy purposes, this is the place to re-enable it.
Many of these features are disabled by default because they pose a possible security threat and have been replaced with better, more secure alternatives. Before re-enabling them, make sure you need the functionality and try to plan for deprecation as soon as possible. Once your applications are updated, return to the tool to disable any unneeded options.
Summary
The Surface Area Configuration tool provides DBAs with a single, easy-to-use method of configuring external security across an entire SQL Server installation. DBAs, especially those upgrading to SQL Server 2005 from an earlier version, will want to become intimately familiar with this tool as quickly as possible, as many legacy features are now disabled by default. Get comfortable with this tool and it will help you to get your servers into a more secure state -- and keep them that way!
About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.
More information from SearchSQLServer.com
Tip: Lock down services with the Surface Area Configuration tool
Tip: Top 10 new features in SQL Server 2005
Learning Guide: SQL Server 2005 Learning Guide
