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."
[IMAGE]
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 Databas
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
e 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.
[IMAGE]
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.
[IMAGE]
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).
[IMAGE]
Figure 4: Configuration for Features
The more important of these options are:
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
