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.
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
action.
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.
- 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
OPENDATASOURCE. - 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
procedures. - 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
procedure. - 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.
