DatabaseManaging the "Surface Area" of SQL Server 2005

Managing the “Surface Area” of SQL Server 2005 content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

As every developer knows by now, Microsoft has focused renewed
attention on security in recent product releases. One of the important
concepts in this effort is surface area. Roughly speaking, a piece
of software has a smaller surface area if there are fewer ways to attack
it: fewer open ports, fewer APIs, fewer protocols, and so on. OSQL Server
2005 takes this concept to the next level by letting you explicitly manage
the software’s surface area. In this article, I’ll show you the tools that
SQL Server offers for this management, using the current (February 2005)
Community Technical Preview build of SQL Server Express as an example.
Some changes are inevitable between now and the final product, but the
concepts should remain relatively stable.

The SQL Server Surface Area Configuration Tool

After you install SQL Server 2005, you can select Microsoft SQL Server 2005
COnfiguration Tool, SQL Server 2005 Surface Area Configuration to launch the SQL
Server Surface Area Configuration Tools. The initial screen, shown in Figure 1,
provides a brief explanation of the surface area concept and offers hyperlinks
that let you start the individual surface area configuration tools. You’ll
almost certainly want to run this tool when you finish installing SQL Server
2005. That’s because SQL Server 2005 defaults to installing with most
features disabled in the interest of security. It’s up to you to
enable the ones that you really need.

The SQL Server Surface Area Configuration Tool

Configuring Services and Protocols

Your first stop in configuration should be the Surface Area Configuration for
Services and Protocols tool. This is where you can turn on (or off) the broadest
swathes of SQL Server 2005 functionality. Figure 2 shows this tool in

Configuring protocols

One thing you’ll discover right off the bat when you install SQL Server 2005
is that you can’t talk to it from across the network. While this does enhance
security by preventing remote attacks, it may not be the most useful
configuration for a shared database server! The protocols node of this tool lets
you enable TCP/IP or named pipes connections so that other machines on your
network can access the new server. Stick to TCP/IP unless you’ve got a known
requirement for named pipes because TCP/IP doesn’t require opening as many ports
in your firewall.

The other node in this tool lets you selectively enable or disable the
various services that collectively make up SQL Server 2005. Depending on which
edition of SQL Server you installed, and which installation options you selected
you can enable or disable any of these services here:

  • Analysis Services
  • Database Engine/li>
  • Full-Text Search Service
  • Integration Services Service
  • MSSQLServerADHelper Service
  • Notification Services Service
  • Reporting Services Service
  • SQL Server Agent Service
  • SQL Server Browser Service
  • SQL Server Writer Service

Configuring Features

After you’ve decided which services to enable, you can proceed to
finer-grained configuration by turning individual features on or offf. As with
many other things in the software world, SQL Server offers tradeoffs between
power and danger. For example, the xp_cmdshell extended stored procedure offers
a tremendous amount of power for system administrators by allowing them to
execute operating system commands from within T-SQL batches. But by the same
token, it means that anyone who gains access to an administrative account can
wreak havoc on the entire server. If you don’t require xp_cmdshell for some
particular purpose, it’s safer to disable it entirely.

The Surface Area Configuration for Features tool, shown in Figure 3, lets
you enable and disable individual features. Depending on which services you have
installed, you’ll see different selections in this tool. Here’s a summary of the
features that you can manage with this tool.

Configuring features

  • Analysis Services Features
    • Ad-hoc Data Mining Queries allow Analysis Services to use
      external data sources via OPENROWSET.
    • Anonymous Connections allow unauthenticated users to connect
      to Analysis Services.
    • Linked Objects enables linking dimensions and measures
      between instances of Analysis Services.
    • User-Defined Functions allows loading user-defined functions
      from COM objects
  • Database Engine Features
    • Ad-hoc Remote Queries allows using OPENROWSET and
    • CLR Integration allows using stored procedures and other code
      written using the .NET Common Language Runtime.
    • Database Mail lets you use the new Database Mail system to
      send e-mail from SQL Server.
    • HTTP Access enables HTTP endpoints to allow SQL Server to
      accept HTTP connections
    • OLE Automation enables the OLE automation extended stored
    • Service Brokerenables Service Broker endpoints.
    • SMO and DMO turns on Server Management Objects and
      Distributed Management Objects.
    • SQL Mail lets you use the older SQL Mail syntax for sending
      e-mail from SQL Server.
    • Web Assistant enables the Web Assistant for automatic output
      to Web pages.
    • xp_cmdshell turns on the xp_cmdshell extended stored
  • Reporting Services Features
    • HTTP and Web Service Requests allows Reporting Services to
      deliver reports via HTTP.
    • Scheduled Events and Report Delivery enables “push” delivery
      of reports.

As you can see, there are a fairly wide variety of features that you can turn
on or off in the features configuration tool. In new SQL Server 2005
installations, you’ll find that the bulk of these features are disabled by
default. This is a radical change from SQL Server 2000, where just about
everything was enabled right out of the box.

Be Smart About Your Surface Area

If you’ve been administering SQL Server installations for a few years, your
first reaction to this new approach to things may be to simply turn everything
back on. After all, one of the great things about SQL Server has always been the
sheer number of powerful features that it manages to pack into a
reasonably-priced package. Why not take advantage of all of those features as
long as they’re still in the box?

The answer is that there’s nothing wrong with taking advantage of powerful
features – when you need them. But when you don’t need them, it’s safer
to turn these features off so that they can’t be misused. If you own a chainsaw,
you keep it in its case in the garage when you’re not using it, rather than on
the couch in the living room (at least, I hope you do!). Treat the powerful but
dangerous features of SQL Server with the same care, and you’ll be better
protected just in case anything does go wrong in the future, whether from a
malicious user or an as-yet-undiscovered security hole.

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer (from which this
article was partially adapted)and Developer to Designer, both from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories