SQL Server Configuration Tools

SQL Server 2005 has a new set of configuration tools. The tools provide an integrated solution for configuration of all SQL Server 2005 products; however, some of the tools are richer and deeper than others. We will spend more time covering these tools (Configuration Manager, Surface Area Configuration Tool) and will provide introductory information about the other tools such as the Usage and Error Reporting tool. The following sections provide descriptions for each tool and its architecture.

SQL Server Configuration Manager

SQL Server Configuration Manager provides the functionality to configure and control Windows services associated with each SQL Server component. As you can see in Figure 3-1, it also allows configuration of client and server network protocols for SQL Server Database Engine. For those of you who are familiar with SQL Server 2000 tools, SQL Configuration Manager is a replacement for legacy tools such as Service Manager, Server Network Utility, and Client Network Utility.

Figure 3-1. SQL Server Configuration Manager tool.

SQL Server Configuration Manager is built on top of Microsoft Management Console (MMC) technology. The tool is an MMC snap-in named SQLServerManager.msc, which is installed in the Windows directory. The tool can be launched several different ways: from its shortcut in the Windows Start menu, from the Services node in Windows Computer Management, or from manual addition of the snap-in to an MMC console. The most common way of launching the tool is from its shortcut from the Windows Start menu. To launch it from Windows Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Configuration Manager. If you want to configure a remote computer, launch SQL Server Configuration Manager from Computer Managementwe will cover this scenario later in this chapter.

Note

SQL Server Configuration does not support configuration of SQL Server 7.0 or SQL Server 2000 installations. To configure older versions of SQL Server, you must use the SQL Server 7.0 or SQL Server 2000 tools. If you need to configure both old and new versions of SQL Server from the same computer, you can install both tools on the same computer.

As depicted in Figure 3-2, most functionality of SQL Server Configuration Manager is accomplished by manipulating Windows services and Registry keys. SQL Server Configuration Manager uses Windows Management Instrumentation (WMI) as a unified way of interacting with the API calls that manage Registry and service operations. Because it uses WMI, SQL Server Configuration Manager does not need to make a connection to any SQL Server components. This means (unlike some of the other tools such as SQL Server Management Studio) SQL Server services do not need to be running for Configuration Manager to configure these services or Registry key values. However, WMI itself has a Windows service, and the dependency of SQL Server Configuration Manager on WMI requires that the WMI service be running. Also, WMI has a dependency on the Distributed Component Object Model (DCOM) and firewall settings for communicating with a remote computer. For further information about opening a DCOM port and adding client applications to the Windows Firewall Exception list, see Microsoft Knowledge Base article found at http://support.microsoft.com/default.aspx?scid=kb;en-us;875605.

Figure 3-2. The high-level architecture of SQL Server Configuration Manager illustrates its dependency on WMI.

Tip

If you are planning to use SQL Server Configuration Manager against a remote computer, you need to make sure DCOM is enabled on both the client and remote computers.

SQL Server Surface Area Configuration

SQL Server 2005 provides a tool named Surface Area Configuration to handle all aspects of surface area configuration for all components. The tool is installed with all editions of SQL Server 2005 and works locally or remotely against a computer.

The architecture of Surface Area Configuration tools looks very similar to that of SQL Server Configuration Manager, except it leverages the object model libraries of the components to configure its surface area (see Figure 3-3). These objects models are SQL Management Objects (SMO) for Database Engine, Analysis Management Objects (AMO) for Analysis Services, and Reporting Services WMI Provider. Both SMO and AMO are installed during installation of Surface Area Configuration on the client. On the server side, Surface Area Configuration has a dependency on Windows WMI service and DCOM.

Figure 3-3. SQL Server Surface Area Configuration has a dependency on WMI, DCOM, SMO, and AMO.

It is a good security practice to disable unused services and features to reduce the surface area of SQL Server. This greatly reduces the risk of security attacks. A good analogy is the threat of a thief breaking into your house. A house with fewer doors and windows has less risk of a break and entry than a house with several doors and windows. Another advantage of reducing surface area is lowering the cost of managing and maintaining your server. The fewer components and features you use, the less cost you incur in management and maintenance of your server. Using the house analogy, the fewer doors and windows, the less time and money needed to maintain locks and door handles.

During the development of SQL Server 2005, the Microsoft SQL Server team made an extensive effort in reviewing security threats to all features and components and decided on providing configuration options for only those features that were subject to security threats. The scope of surface area configuration in SQL Server 2005 was defined in three categories:

• By default, which components should be enabled or disabled

• Capability to enable or disable remote connections

• Capability to disable or enable higher-risk features in each component

In addition, the SQL Server team's goal was to design a product that is secure by default, yet it is usable and does not break legacy applications. The team also had to provide a tool for customers to enable those features that were switched off by default, and describe the effects of enabling or disabling a particular feature or component.

There are two variations of the Surface Area Configuration tool: a graphical user interface (GUI) tool and a command line tool. Both tools work with SQL Server 2005 instances, but they do not work with older versions of SQL Server, such as SQL Server 7.0 or SQL Server 2000.

GUI Tool

Reducing the surface area of your server reduces the exposed surface area for security attacks, but it is not a sufficient measure for securing your SQL Server installation. In addition to reducing the surface area of your SQL Server, you must implement the general security practices to prevent unauthorized access to your server. SQL Server Books Online has a number of security topics that you may find useful is securing your server.

To launch the surface area configuration GUI tool from Windows Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Surface Area Configuration. The main dialog of Surface Area Configuration tool (see Figure 3-4) provides links to launch additional dialogs for enabling or disabling services, remote connections, and features. In addition, there is a link for the tool to connect to a remote computer.

Figure 3-4. The main panel of Surface Area Configuration tool.

Command Line Tool

The command line tool is called SAC.EXE and it is installed in the Shared folder of your SQL Server installation (for example, if SQL Server is installed on the C drive, you can find it at C:\Program Files\Microsoft SQL Server\90\Shared\SAC.EXE). The functionality of the GUI and command line tool is very similar except the command line tool provides the additional functionality to export and import surface area settings to and from a file. This functionality enables deployment scenarios, especially for large enterprise customers with hundreds or thousands of servers. We cover this scenario in detail later in this chapter.

The SAC command line utility has a number of switches that control the surface area settings that are exported or imported from a file. To get a listing of SAC commands, run SAC/? in a command window:

C:\Program Files\Microsoft SQL Server\90\Shared>sac /? Imports and exports SQL Server 2005 surface area settings.   sac {in | out} filename [-S computer_name]       [-U SQL_login [-P SQL_password]]       [-i instance_name ]       [-DE] [-AS] [-RS] [-IS] [-NS] [-AG] [-BS] [-FT]       [-F] [-N]  [-T]  [-O]       [-H | -?]   in               Import the surface area settings from a file.   out              Export the surface area configuration settings                    from an instance to a file.   filename         Full path of the import or export file.   -S computer_name Name of a remote computer. Default is local                    computer.   -U SQL_login     SQL Server Authentication login for the Database                    Engine connection. Default is Windows                    Authentication.   -P SQL_password  Password for SQL_login.   -i instance_name SQL Server instance to run sac against. Default                    is all instances on the specified computer.   -DE              Import or export Database Engine settings.   -AS              Import or export Analysis Services settings.   -RS              Import or export Reporting Services settings.   -IS              Import or export Integration Services settings.   -NS              Import or export Notification Services settings.   -AG              Import or export SQL Server Agent settings.   -BS              Import or export the SQL Browse service settings.   -FT              Import or export the Full-Text Search service                    settings.   -F               Import or export the state of features.   -N               Import or export the state of network protocols.   -T               Import or export the state of Windows services.   -O               Name of the file that receives command-line output.   -H | -?          Display command syntax. Other arguments are                    ignored. 

Secure by Default and Upgrade

The goal behind SQL Server 2005 "secure by default" is to provide a secure environment for new installations, but not to break customer applications after an upgrade. For example, xp_cmdshell is a legacy Database Engine extended stored procedure that was initially introduced in SQL Server 6.5. xp_cmdshell is a powerful utility for executing operating system commands from a database engine and is used in some client applications and administration scripts. In new installations of SQL Server 2005 Database Engine, xp_cmdshell is disabled by default. However, to avoid breaking legacy applications, xp_cmdshell remains enabled after a previous installation of SQL Server is upgraded. On the other hand, this upgrade behavior is applicable only to older features of SQL Server. The new features of SQL Server remain disabled after an upgrade. For example, integration of Common Language Runtime (CLR) in Database Engine is a new feature of SQL Server 2005 that allows development of stored procedures, user-defined functions, triggers, and user-defined types with any Microsoft .NET Framework language. This new feature is disabled on both new and upgraded installations of Database Engine.

Reporting Services Configuration

The Reporting Services Configuration tool (see Figure 3-5) provides a vehicle to configure and control Reporting Services settings on a SQL Server 2005 installation. This tool can also be used for the deployment of Reporting Services, if the user has chosen the option to just copy the files during the initial installation (from the SQL Server Installation Wizard). Similar to SQL Configuration Manager, this graphical user interface tool takes advantage of WMI to manipulate the configuration files and Registry keys. The tool can be used to connect to both local and remote installations of SQL Server 2005 Reporting Services.

Figure 3-5. SQL Server Reporting Services has a dedicated tool for all aspects of its configuration.

Usage and Error Reporting Configuration

SQL Server 2005 provides the functionality to send information about product usage and serious system errors to Microsoft. You can use the Usage and Error Reporting tool to opt in or out of this option (see Figure 3-6). To launch the tool, go to Windows Start menu, point to Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Error and Usage Reporting. The tool provides two views: simple and advanced for the error and usage reporting.

Figure 3-6. Error and Usage Report Settings enable you to opt in or out of sending information about product usage and system errors to Microsoft.

In the simple view, you can choose to opt in or out for all components and all instances on the computer. In the advanced view, a grid view represents all SQL Server instances and components where you can opt in or out of sending information for a particular component. Sending this information enables the SQL Server team to identify and fix the most commonly encountered bugs and improve the product in future releases of SQL Server. Another advantage of enabling this feature is that when a fatal error occurs, you may see a response from Microsoft in the Windows Event Log that points to a Microsoft Knowledge Base article. These articles are written based on highest hits from users, so errors and events hit most often will have regularly updated articles.

Error and Usage Report Settings cannot configure a remote computer. If you need to configure a remote computer, you must log on to the computer or use the Remote Desktop tool to run Error and Usage Report Settings on the remote computer.