IT Training Courses
Microsoft Gold IT Training Partner
Contact Us
800-326-1044

Live Help

Our Learning Consultants are standing by available to help.

Search Courses:


IT Courses Offered In:


QuickStart uses Lync for remote classes and QSLive

Microsoft Gold Partner

This site runs on .NET 4.0 and SQL Server 2008 powered by Windows Server 2008 R2 Hyper-V. Built using Visual Studio 2010.
IT Training Newsletters SQL Server 2005 Surface Area Configuration

SQL Server 2005 Surface Area Configuration

by Saskia Schott
Principal Instructor for SQLSoft+
MOF, MSF, MCSA, MCSE, MCDBA, MCT

Microsoft has changed its approach to designing software over the last five years; their approach is now that software will be secure by Design, Default and Deployment. In line with this philosophy, code has been scrutinized for security flaws. In addition, with the release of SQL 2005 in November, SQL Server will be secure by default when installed as a fresh install. To enable those of us who want to migrate our SQL Servers, SQL Server has designed the upgrade so that whatever settings you have on your current SQL Server will be maintained when you upgrade.

But, what if you weren't as concerned as you should be about SQL Server vulnerabilities? Before you didn't have an easy way of managing services, features, and connections in SQL Server. Now you do.

The SQL Server 2005 Surface Area Configuration tool is one of the four menu choices you have when you choose Configuration Tools. When you start the tool, it dynamically identifies the instances, components and features you have installed.

The tool allows you to disable unnecessary services, disable unneeded protocols, and disable unused features of SQL Server components. It is called the Surface Area Configuration tool because, for each service you have running and for each component you install, you increase the potential attack surface for hackers, and increase the management requirements for your SQL Server. By giving you a tool that in one interface shows you what your SQL Server has running and allowing you to turn that off, or disable it, you can reduce your Surface Area—your vulnerability and your management load.

For the relational database engine, which I will refer to as the Database from now on, for Analysis Services, for Reporting Services, Integration Services (formerly called DTS) and Notification Services, you can manage Services. For the Database, Analysis Services and Reporting Services, you can manage features and you can manage remote connections for the Database and Analysis Services.

In the tool, if you choose to manage services and connections, you can view the service information about your local server by instance or by component, and then reconfigure it.

When you View by component, you see Database Engine, Analysis Services, SQL Server Agent, Full Text Search and the SQL Server Browser. The View by instance tab shows what components are installed on each instance, including SQL Express if installed.

For each service, you can select to Start, Stop, Pause or Resume, as appropriate. However, unlike the Services MMC, you cannot with one click Restart. You can also configure whether that component will be available for Local connections only, or available for local and remote connections.

If you choose availability for local and remote connections, you can choose whether the protocols allowed will be only TCP/IP, only Names Pipes, or both.

So in one interface you can manage services, connections and protocols.

In the other interface, you can configure features, and again, you can view them by instance, or by component. For SQL Servers Database Engine, you can turn on or off the following list of features:

  • Ad-Hoc Remote Queries
  • CLR Integration
  • Database Mail
  • Dedicated Administrator Connection
  • Native Web Services
  • OLE Automation
  • Service Broker
  • SQL Mail
  • xp_cmdshell
  • Web Assistant.

In a fresh install, all these features are turned off. In an upgrade, those features such as xp_cmdshell that you had in your previous version of SQL Server would retain their configuration, while the new features would be turned off. In addition, OPENROWSET and OPENDATASOURCE support are also turned off by default, and can be configured in this window, should you need to use them to connect to remote data sources with or without linked or remote servers.

For Analysis Services, you can enable or disable the following features:

  • Ad-hoc Data Mining Queries
  • Anonymous Connections
  • User-Defined Functions
  • Linked Objects.

The great thing about this tool is that after you install SQL Server 2005, you are asked if you want to run the Surface Area Configuration Tool. And you should. Whether you perform an in-place upgrade, or a fresh install, you should consciously evaluate what the surface area of your SQL Server 2005 installation will be.

Recommended Classes