Common Configuration Scenarios
SQL Server configuration tools enable a number of scenarios that you may encounter in your daily activities. The following sections describe a number of the common configuration scenarios and how to use the configuration tools to perform these tasks.
Identifying SQL Server Instances and Components on a Computer
Whether it is your initial configuration or part of your daily administration of SQL Server, you will encounter situations where you need to identify instances of SQL Server that are installed on a given computer. You may also need to find out the physical association of components and instances, and the state of services for each of the installed components. But before we describe how you can perform this sort of tasks, you need to understand the relationship between SQL Server instances, components and services.
As described in Chapter 2, SQL Server allows multiple instances of SQL Server to run simultaneously on the same computer. Each instance of SQL Server consists of a number of components, and each component has a separate set of services. For example, on a given computer that has two SQL Server instances named Accounting and Sales, the Accounting instance may have a Database Engine and a Reporting Services component, and the Sales instance may have a Database Engine and an Analysis Services component. End-user applications can connect to any of these components in the Accounting or Sales instances the same way they would connect to these components if they were installed on two separate computers. You should also know that not all SQL Server components can be installed in multiple instances on the same computer. For example, Integration Services is not associated with any instances of SQL Server and it can only be installed once on a computer. There are also services that are shared between all instances on a computer. For example, SQL Server Browser service, which provides instance name resolution for all instances, is not associated with a particular instance. In addition, there are auxiliary services that are not exposed to client applications but support other services such as SQL Server Active Directory Helper and SQL Server VSS Writer. These services are typically installed along with the main services and you do not need to install them explicitly.
Table 3-1 Describes SQL Server components and their corresponding services.
To identify SQL Server instances and components on a computer, you can use SQL Server Configuration Manager or Surface Area Configuration tools.
To launch SQL Server Configuration Manager, point to Windows Start menu, All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Configuration Manager. SQL Server Configuration Manager has two panes. Selecting an entry in the left pane displays members of the entry in the right pane. To view services, select SQL Server 2005 Services in the left pane and the right pane (see Figure 3-7) displays all services that are installed on the computer.
Figure 3-7. The right pane of Configuration Manager displays information about SQL Server services.
The service names in the right pane of SQL Server Configuration manager appear in the format, component name (instance name). For example, SQL Server Analysis Services (Sales) refers to the service for the Analysis Services component in the Sales instance of SQL Server. The instance name MSSQLSERVER refers to the default instance. SQL Server Reporting Services (MSSQLSERVER) refers to the service for the Reporting Services component in the default instance of SQL Server. You may have noticed that the association of instances with services is not very distinguishable in SQL Server Configuration Manager. In fact, if you have multiple instances and several components in each of the instances, the list of services and instances becomes too long and it becomes difficult to understand the relationship between instances and components. In such scenarios, you can use the Surface Area Configuration tool to discover the instances.
In addition to discovery of SQL Server instances and components on a computer, you can use the Surface Area Configuration tool to easily identify the relationship between instances and components.
To launch Surface Area Configuration from Windows Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and select SQL Server Surface Area Configuration. To view or change SQL Server service states, and to enable or disable remote connections, select Surface Area Configuration for Services and Connections from the main panel. The Surface Area Configuration for Services and Connections dialog has two panes. The left pane (see Figure 3-8) provides a hierarchal view of the installed instances and components, and the right pane displays service information for the selected component in the left pane.
Figure 3-8. The left pane of SQL Server Surface Area Configuration provides a hierarchal view of installed instances.
The information in the left pane of Surface Area Configuration can be used to quickly answer questions like:
Let's go through an example and discover how you can use the tool to answer these questions. In Figure 3-8, the View by Instance tab in the left pane shows four instances of SQL Server, namely MSSQLSERVER (or default instance), ACCOUNTING, SALES, and FlightInstance. In addition, you see Integration Services and SQL Server Browser components that are not associated with any instances of SQL Server. Figure 3-8 also shows that the ACCOUNTING instance has four components: Database Engine, Reporting Services, SQL Server Agent, and Full-Text Search.
If you switch to View by Component (see Figure 3-9), you can view the instances by components. The information now can be used to answer questions such as
Figure 3-9. The left pane of SQL Server Surface Area Configuration provides a hierarchal view of installed components.
Following the previous example, Figure 3-9 now displays the same information by component. The left pane displays that there are eight installed components on this computer. Furthermore, the Database Engine is installed in three instances, namely MSSQLSERVER (or default instance), ACCOUNTING, and SALES.
You should be aware that not all SQL Server services described in Table 3-1 are displayed in SQL Server Configuration Manager or the Surface Area Configuration tool. Auxiliary services such as SQL Server Active Directory Helper and SQL Server VSS Writer are not exposed in these tools. You do not need to configure these services, and their initial configuration during setup is sufficient to run these services. However, in emergency situations, you may want to configure or manage these services. In that case, you can view and configure these services from the Windows Service Manager. You can launch the Windows Service Manager from the Start menu by pointing to All Programs, Administrative Tools, and selecting Services.
Starting, Stopping, and Viewing State of Services
As described earlier, there is a Windows service associated with each component of SQL Server. SQL Server components use Windows services to connect and respond to requests from client applications. Stopping a service results in refusal of new connections and termination of the existing connections. There are a number of scenarios where you need to stop, pause, or start a service. For example, you may have to terminate all connections to investigate a problem or to perform a system update. Similarly, a DBA may need to pause the Database Engine service to deploy a new build of an application. There are circumstances where you need to change a setting, but the new setting will not take effect until the service is stopped and restarted. You can also change the behavior of service startup. You may need to change the startup behavior of a service so it does not automatically start when the operating system is started. You can also view the state of a service. An application may suddenly stop connecting to the server and you need to view the state of the service to isolate the problem to either service or network issues.
SQL Server Configuration Manager is the primary tool for starting, stopping, and viewing SQL Server service states. But you can also use Surface Area Configuration to perform these tasks. The right pane of SQL Configuration Manager provides a summary view for the state of all services on a given computer (see Figure 3-10). SQL Server Configuration does a better job of grouping components by instances.
Figure 3-10. The right pane of SQL Server Configuration Manager provides a summary view for the state of all services.
A service could have one of these three states: Stopped, Started, or Paused. In the right pane of SQL Server Configuration Manager, the state of the service is displayed in the State column and in a small traffic light icon next to its name. In Figure 3-10, you can see that Integration Services and Notification Services are stopped and SQL Server (MSSQLSERVER), which is the default instance of SQL Server Database Engine, is paused. The right pane also has a column named Start Mode, which describes when and how a service is started. A service can have one of the three start modes: Automatic, Manual, and Disabled. In Figure 3-10, SQL Server Reporting Services has start mode of Manual, and the Notification Services instance named NS$FlightInstance is disabled.
To start, stop, or resume a service, right-click on the service and select the appropriate action. You can select the Properties option to view additional information about the service, as shown in Figure 3-11.
Figure 3-11. SQL Server services can be started, stopped, paused, resumed, and restarted from the right pane of SQL Server Configuration Manager.
Alternatively, you can start, stop, pause, and resume a service from the Surface Area Configuration tool (see Figure 3-12). To launch Surface Area Configuration for Services and Connections, select a component in the left pane and click on one of the buttons to Start, Stop, Pause, or Resume the service.
Figure 3-12. SQL Server Surface Area Configuration tool can be used to stop, start, pause, and resume services.
Configuring Service Account and Password
SQL Server services need to access system resources such as files, folders, and protected Registry keys. This requires that the service account logs on to an account to access these resources. The account to which a service logs on can be a built-in system account or a user account. To specify a service account, launch SQL Server Configuration Manager (see Figure 3-13), select the service, and choose Properties. In the service properties dialog, you can use either one of the built-in accounts or a user account.
Figure 3-13. SQL Server Configuration Manager provides the functionality to view or change a service account.
Changing a service logon account requires restarting the service. However, new in SQL Server 2005, changes to the account password do not require restarting the service.
Whether you decide to log on as one of the built-in accounts or a user account, it is highly recommended that you run SQL Server services with the lowest possible privileges. This minimizes the escalation of privileges in the event that your service is compromised.
Do not use Windows Service Manager to change SQL Server service accounts. Properly changing SQL Server service accounts requires setting special permissions on the file system and Registry keys that can be performed only with SQL Server Configuration Manager. Always use SQL Server Configuration Manager to change service account information.
Configuring Advanced Service Settings
In addition to the status and startup mode, additional configuration properties of a service can be viewed or manipulated in the Advanced tab of the Service Properties dialog (see Figure 3-14). Although some of this information is available if you make a connection to the server and execute system queries, SQL Configuration Manager also makes this information available without a connection. This is useful for situations in which the service is not running and you need to look up a particular property quickly, especially on a remote computer.
Figure 3-14. SQL Configuration Manager enables you to view and change advanced properties of a service without a connection.
Advanced properties can be used to answer questions such as
Following is a description of the advanced properties and how you can use these properties to answer these questions.
Clustered and Virtual Server Name
As mentioned in Chapter 2, a service can be installed as a resource of a clustered server or as a regular standalone service. You can use the values of the Clustered property to quickly identify whether a service is clustered. You can look up the Virtual Server Name that hosts the instance of SQL Server. These two properties are particularly useful for identifying SQL Server clustered services on remote computers.
Customer Feedback Reporting
SQL Server 2005 provides the option for its users to send information about their product usage to Microsoft. You can use the Customer Feedback Reporting property to opt in or out of sending usage information for a service. Notice Customer Feedback Reporting performs the same functionality as the Error and Usage Reporting tool, but at a more granular level. Customer Feedback Reporting property controls a service, whereas the Error and Usage Reporting tool controls one or more components of an instance, or all components of all instances of SQL Server. The functionality of the Error and Usage Reporting tool is described earlier in this chapter.
Dump Directory and Error Reporting
When a service has a fatal error, SQL Server creates a memory dump and optionally sends it to the SQL Server team. The Microsoft SQL Server team uses the error reports to improve the functionality and quality of SQL Server products. You can use the Dump Directory property to specify the location of the memory dump, and Error Reporting to specify whether you want to send the memory dump to Microsoft. Error Reporting performs the same functionality as the Error and Usage Reporting tool but at a more granular level. The Error Reporting property controls a service, whereas the Error Reporting tool controls one or more components of an instance, or all components of all instances of SQL Server.
Instance ID displays the internal identifier of this instance of SQL Server assigned by the system during an installation of the instance. Instance ID is used by the system for installing instance files in a folder named as the Instance ID. Instance ID is a read-only field in the format, SQL.1, SQL.2, SQL.3,... and is different from Instance Name, which is specified by the user during installation of an instance. For example, on a computer with multiple instances of SQL Server, an instance could be named Sales with an Instance ID of MSSQL.6. This means all the files relevant to the Sales instance are installed under the folder C:\Program Files\Microsoft SQL Server\MSSQL.6. Without knowing the Instance ID, it would be difficult for the user to find the database or backup files of the Sales instance.
Language is a read-only property that displays the identifier of the default language for SQL Server messages. The mapping between the language identifier and language name is listed in Table 3-2.
Registry Root is a read-only property that displays the root of the Registry that holds the keys for the instance of SQL Server.
Service Pack Level
Service Pack Level is a read-only property that displays the service pack level for the instance of SQL Server. Service Pack level of 0 implies that no service packs have been applied to the instance.
Look up the value of the Service Pack Level property for situations in which you need to determine the version of SQL Server service pack on a local or remote computer, even if the service is not running.
Startup Parameters specifies the parameters that are applied when the instance of SQL Server is started. Each of the parameters is separated by a semicolon from the previous parameter. For further information about startup parameters and their supporting scenarios, see "Configuring Database Engine Startup Parameters" in this chapter.
Stock Keeping Unit Name
Stock Keeping Unit Name is a read-only property that displays the instance edition information. The edition value could be Express Edition, Workgroup Edition, Standard Edition, Enterprise Edition, or Evaluation Edition.
Version is a read-only property that displays the version of SQL Server service.
You can use the value of the Version property for situations in which you need to determine the version of SQL Server service on a local or remote computer, even if the service is not running.
Configuring Database Server Network Protocols
SQL Server Database Engine and client applications communicate via network packets inside a standard communication protocol. This is done using network libraries on both server and client computers. The network packets are formatted in Tabular Data Stream (TDS) format. The network protocol could be one of the SQL Server-specific protocols, namely Shared Memory, TCP/IP, Named Pipes, or Virtual Interface Adapter (VIA). There is no need to install network libraries on the server computer; they are always installed as part of the database engine installation. However, the client network libraries need to be installed as part of the SQL Native Client installation. There is no need for the system administrator to configure the network packet or network libraries on a client or server computer. However, the communication protocols need to be configured on both the client and server computers.
To enable and configure network protocols for an instance of Database Engine server, launch SQL Server Configuration Manager on the server computer, expand SQL Server 2005 Network Configuration in the left pane and select the instance. The right pane (see Figure 3-15) displays all available SQL Server 2005 protocols on the server and each protocol's status.
Figure 3-15. The right pane of SQL Server Configuration Manager provides a list of available network protocols for a Database Engine instance.
A Database Engine instance could use one or many network protocols to communicate with client applications. To enable a protocol (see Figure 3-16), select the protocol, right-click, and choose Enable.
Figure 3-16. SQL Server Database Engine network protocols can be enabled or disabled from the right pane of SQL Server Configuration Manager.
Additionally, you can choose Properties to view or change network properties. SQL Server Configuration Manager reads and writes the status of protocols and their properties from the Windows Registry. These properties are read when the Database Engine service starts. Therefore, making any changes to the protocols while the service is running has no effect.
Changes to the state of server network protocols and their properties require a service restart for the changes to take effect.
Configuring Database Client Application Network Protocols
As described earlier, a Database Engine server can be configured to communicate with clients using one or more network protocols. A client must also be configured to communicate with the server using one of the enabled protocols on the server.
To enable and configure client network protocols for an instance of Database Engine, launch SQL Server Configuration Manager on the server computer, expand SQL Server 2005 Network Configuration in the left pane, and select the instance. The right pane (see Figure 3-17) displays all available SQL Server 2005 protocols on the client computer and each protocol's status.
Figure 3-17. SQL Server Configuration Manager provides a list of available client network protocols on a computer.
A client computer could use one or many network protocols to communicate with a database engine instance. To enable a protocol (see Figure 3-18), select the protocol, right-click, and choose Enable. Also, a server and client could be configured to use multiple network protocols. When a client tries to connect to a server, it sequentially tries each of the enabled protocols until there is a match with one of the protocols on the server. The order in which the client tries each protocol is stored in the Registry, and it can be viewed and changed in SQL Server Configuration Manager. After a connection is established, the client and server continue to communicate through the specified protocol until the connection is terminated.
Figure 3-18. SQL Server client network protocols can be enabled/disabled and reordered from the right pane of SQL Server Configuration Manager.
The Shared Memory protocol is not available to client applications on remote computers and is available only for client applications that run on the same computer as the server instance.
For troubleshooting scenarios where you suspect client network and server protocols are not configured properly or client applications cannot connect to the server, use the shared memory protocol to connect locally to the server.
The SQL Server client network protocols are installed as part of the SQL Native Client library installation. If the client and server are on the same computer, the SQL Native Client library is installed automatically during the installation of the SQL Server instance. If the client is a remote computer, you must install the SQL Native Client manually by running sqlncli.msi (or sqlncli_x64.msi for Windows 64-bit). The file sqlncli.msi can be found on SQL Server 2005 DVD or media. There are two options for configuring network protocols on a remote client computer. You can either install and run SQL Configuration Manager on the remote computer, or run SQL Server Configuration manager on a remote computer and connect to the client computer. To connect SQL Server Configuration Manager to a remote computer, see the following "Configuring a Remote Computer" section.
Configuring a Remote Computer
Configuring a remote computer is a common administration task. However, connecting SQL Configuration Manager to a remote computer is not intuitive (see Figure 3-19). If you launch SQL Server Configuration Manager from the Windows Start menu, it can connect to only the local computer. However, you can launch SQL Server Configuration Manager from Computer Management or SQL Server Management Studio to connect to a remote computer.
Figure 3-19. Computer Management provides the functionality for SQL Server Configuration to connect to a remote computer.
To connect SQL Server Configuration Manager to a remote computer from Computer Management, follow these instructions:
Alternatively, you can connect SQL Server Configuration Manager to a remote computer from the Registered Servers window in SQL Server Management Studio (see Figure 3-21).
Figure 3-21. SQL Server Management Studio provides the functionality for SQL Server Configuration Manager to connect to a remote computer.
Encrypting Connections to Database Engine
You can enable encrypted connections between clients and an instance of SQL Server Database Engine by configuring a certificate for the Database Engine. Note that the same certification authority must issue a certificate for the clients and Database Engines. To configure a certificate for an instance of Database Engine on a computer, you must first install a certificate on the computer and then configure the Database Engine instance to use the certificate and accept encrypted connections.
To install certificates on a computer follow these steps:
You can install a certificate on a remote computer. Follow steps 1 to 6 on a client computer and in step 7 choose the option Another computer. Type the name of the remote computer in the Select Computer dialog. Alternatively, after you have added the certificates snap-in to an mmc console, you can right-click on the Certificates (Local Computer) node, select the menu option Connect to Another Computer, and enter the name of the remote computer in the Select Computer dialog.
To configure a Database Engine instance to use the certificate and accept encrypted connections, follow these steps:
Configuring Database Engine Startup Parameters
As discussed earlier, each instance of SQL Server Database Engine has a service that needs to be started for the instance to service client requests. When a service starts, it reads its startup parameters from the Registry. Some of these startup parameters, such as location of master database and log files, are specified during installation of the service. Other parameters, such as behavior of the engine and enabling or disabling of features, can be specified by the system administrator after the service installation. You can use SQL Server Configuration Manager to view or change the startup parameters.
To view or change the startup parameters of a SQL Server Database Engine instance, launch SQL Server Configuration Manager, right-click on the service, and select the Properties option in the menu (see Figure 3-24). On the Properties dialog, choose the Advanced tab and select Startup parameters in the grid.
Figure 3-24. Service Properties dialog in SQL Server Configuration Manager provides the functionality to view or change the startup parameters.
Each startup parameter is specified by a dash character followed by a letter and its value. A Database Engine service has multiple startup parameters. Each parameter is separated by a semicolon. For example, in Figure 3-24 there are three startup parameters.
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\DATA\ master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ mastlog.ldf
The first parameter starts with -d, which specifies the location of the master database data file. The second parameter starts with -e which specifies the location the operation log file. The last parameter starts with -l, which specifies the location of the master database log file.
Starting Database Engine in Single User Mode
There are situations in which a DBA needs to change certain Database Engine configuration parameters or recover the master database, which require starting the Database Engine in single-user mode. This can be done by following the same instructions as in the previous scenario to edit the startup parameters and append ;-m to the startup parameters. For example, to start the service in Figure 3-24 in single-user mode, the startup parameters would be
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ mastlog.ldf;-m
Remember, any change to service startup parameters requires a service restart for the change to take effect.
Starting Database Engine with a Trace Flag
Database Engine trace flags are startup parameters that change the server behavior or enable/disable a particular feature. Starting a server with a trace flag is typically temporary and it is used for troubleshooting server problems, particularly to investigate performance issues. To start a database engine with a particular trace flag, you need to append the trace switch t followed by the trace flag number. For example, trace flag 1204 registers the resources and types of locks participating in a deadlock and the current command affected in the database engine log To start the database engine in Figure 3-24 with trace flag 1204, the startup parameters would change to
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ mastlog.ldf;-t1204
Enabling Database Mirroring
Database Mirroring is a powerful feature for increasing availability of a database. In the first release of SQL Server 2005, this feature is not supported and is available for evaluation purposes only. However, there is a good chance that this feature may be supported in the future service packs or releases of SQL Server 2005. To enable database mirroring for a particular instance of Database Engine, change the service startup parameter to include trace flag 1400 (see Figure 3-25). Follow the same instruction as in the previous section and append ;-t1400 to the startup parameters.
Figure 3-25. Database Mirroring can be enabled by adding trace flag 1400 to startup parameters.
After appending the trace flag 1400 to the startup parameters, you need to restart the service to enable Database Mirroring. To make Database Mirroring fully functional, you need to further configure it in SQL Server Management Studio.
Configuring Surface Area of SQL Server
As mentioned before, there are three concepts behind surface area configuration in SQL Server 2005:
The following sections describe each aspect of surface area configuration.
Reducing 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 in securing your server.
Reducing Surface Area of SQL Server by Shutting Down Unused Components
You can reduce the surface are of SQL Server by stopping and disabling unused components and their corresponding services. These services include
Each SQL Server service has a Startup attribute that specifies when the service is started. A service could have one of the startup modes: Automatic, Manual, and Disabled. A service with Automatic startup is automatically started when the operating system starts. A service with Manual startup remains stopped until the system administrator manually starts the service or a dependent service starts the service. A service with Disabled startup cannot be started until its Startup attribute is changed to Manual or Automatic.
You can use the Surface Area Configuration tool to stop and disable SQL Server services. Launch Surface Area Configuration for Services and Connections, select the unused component in the left pane, and click on Stop in the right pane to stop its service (see Figure 3-26). If you want to further disable the service, you can set its Startup type to Disabled. Conversely, the tool allows starting, pausing, and resuming of a service.
Figure 3-26. You can start, stop, pause, and resume SQL Server services with the Surface Area Configuration tool.
Although at a glance you may think stopping and starting a component in the Surface Area Configuration tool merely stops and starts its corresponding service, the tool actually performs more than service manipulation for some of the services. For example, stopping and starting Reporting Services also enables and disables the Reporting Services web services and URLs.
For this reason, it is recommended that you use the Surface Area Configuration tool for disabling or enabling all SQL Server 2005 components.
Alternatively, you can use SQL Server Configuration Manager to start and stop a SQL Server service and change its startup mode. You already saw how to start and stop a service earlier in this chapter. To disable a service, launch SQL Server Configuration Manager, select a service on the right pane, right-click, and choose Properties. This launches the Service Properties dialog (see Figure 3-27). In the Service Properties dialog click on the Service tab and set the Start Mode to Disabled.
Figure 3-27. Start mode of a service can be specified in SQL Server Configuration Manager.
Reducing Surface Area of SQL Server by Disabling Remote Connections
A SQL Server client application can reside either locally on the same computer as the SQL Server installation or remotely on a separate computer. One of the effective ways to reduce security attacks is to disable the capability for remote client applications to connect to SQL Server.
SQL Server 2005 provides the functionality to disable remote connections for Database Engine and Analysis Services components. For Database Engine, you do so by disabling network protocols that are used for remote connections. A remote client application uses one of the protocols TCP/IP, Named Pipes, or VIA to connect to a SQL Server Database Engine. So, by disabling TCP/IP, Named Pipes, and VIA protocols, you can disallow remote connections to your server. A local client application can use Shared Memory protocol to connect to the server. You can further disable Shared Memory protocol, but then no local client applications including SQL Server tools can connect to manage the server. In most cases you want to enable Shared Memory protocol, so tools such as SQLCMD and Management Studio can connect to the server.
To enable or disable remote connections for Database Engine and Analysis Services, launch Surface Area Configuration for Services and Connections, select the instance, the component, and Remote Connection in the left pane (see Figure 3-28). The right pane displays the current remote connection state of the selected component. To disable remote connections, select Local connections only. To enable remote connections, select Local and remote connections and choose one or both of the TCP/IP and Named Pipes protocols. It is good security practice to only enable the protocol that is needed by your application but not both protocols. Enabling or disabling remote connections for a component requires restarting the service associated with the component.
Figure 3-28. Use the Surface Area Configuration tool to enable remote connections to Database Engine and Analysis Services.
By default, remote connections are disabled in certain editions of SQL Server 2005 such as Express, Evaluation, and Developer editions, and are enabled in all other editions. After installing SQL Server, you can use the Surface Area Configuration tool to enable or disable remote connections.
Reducing Surface Area of SQL Server by Disabling Features in Database Engine, Analysis Services, and Reporting Services
The third aspect of surface areas configuration that can reduce the surface areas of SQL Server, as mentioned previously, is concerned with the features in Database Engine, Analysis Services, and Reporting Services.
Enabling and Disabling Database Engine Features
SQL Server 2005 Database Engine provides the functionality to configure surface area by disabling or enabling its features. The option to enable or disable is not available for all features of Database Engine. The Microsoft SQL Server team made an extensive effort in reviewing all product features and decided on providing configuration options only for those features that were subject to a security threat. The goal of the team has also been to design a product that is secure by default. Most of these features are disabled by default. Accessing any of the disabled features returns an error indicating that the feature is disabled for the purposes of surface area reduction. For example, executing the stored procedure xp_cmdshell when it is disabled generates the following error:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
To enable or disable a feature of Database Engine, launch Surface Area Configuration from the Windows Start menu, select Surface Area Configuration for Features, select the Database Engine instance and the feature in the left pane, and enable or disable the feature on the right pane (see Figure 3-29). The right pane also provides a provisionary description of the feature, with additional details available if you click on Help or from Books Online.
Figure 3-29. Use the Surface Area Configuration tool to enable or disable Database Engine features.
Enabling or disabling SQL Server Database Engine features for a particular instance requires the Database Engine service for that instance to be running. In fact, the left pane of the Surface Area Configuration for Features dialog displays only components that are currently running. For example, in the scenario illustrated in Figure 3-29, the default instance (MSSQLSERVER) has two components: Database Engine and Analysis Services. If Database Engine is stopped, the left pane shows only Analysis Services under MSSQLSERVER instance. If Analysis Services is also stopped, the left pane does not display the MSSQLSERVER instance at all. In the event that all components are stopped, launching of Surface Area Configuration for Features dialog displays the error message show in Figure 3-30.
Figure 3-30. Enabling or disabling of features of a SQL Server component requires its service to be running.
The following sections describe the Database Engine features that could be enabled or disabled, along with a brief description of each feature.
Ad-hoc Remote Queries
The two T-SQL functions OPENROWSET and OPENDATASOURCE provide the functionality to run ad-hoc remote queries without necessarily defining a linked server. By default, these two functions are disabled on new installations of SQL Server 2005 Database Engine but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.
The integration of Common Language Run-time (CLR) inside SQL Server 2005 Database Engine provides the functionality to create and execute CLR-based objects. These objects include types, user-defined functions, stored procedures, and triggers. By default, running CLR objects is disabled, which means you can create, alter, or drop CLR-based objects but you cannot access these objects and run the CLR code.
The dedicated administrator connection (DAC) is a high-priority connection to the server for troubleshooting when the server is "hung" and does not accept new connections. This feature is always available for tools and applications that run locally on the same computer as Database Engine. By default, DAC is disabled from a remote computer.
Database Mail is a new subsystem for sending e-mail messages from Database Engine. Database Mail relies on Service Broker and a few extended stored procedures that are disabled by default. Disabling Database Mail while it is busy processing emails in its queues does not stop Database Mail from sending the emails. If you need to stop Database Mail immediately, execute the stored procedure msdb.dbo.sysmail_stop_sp. For more information about Database Mail see Chapter 10.
SQL Mail is the legacy subsystem for sending and receiving email messages from Database Engine. SQL Mail relies on a few extended stored procedures that are disabled by default in a new installation of SQL Server Database Engine but enabled in installations upgraded from SQL Server 2000 or SQL Server 7.0.
The dependency of SQL Mail on Microsoft Outlook has been problematic for many customers. In SQL Server 2005, Microsoft has introduced a new subsystem called Database Mail, which uses SMTP for sending email messages. SQL Mail will be removed in future versions of SQL Server. If you are planning to use SQL Mail, consider using Database Mail instead.
Native XML Web Service
Native XML Web Service is a new feature in SQL Server 2005 that provides access to Database Engine through use of Simple Object Access Protocol (SOAP) messages over user-defined HTTP endpoints. HTTP endpoints can be stopped and disabled to reduce risk of malicious access to Database Engine.
OLE Automation provides functionality to create and access OLE Automation objects from Database Engine. The OLE Automation functionality is exposed through a number of extended stored procedures, namely, sp_OAGetProperty, sp_OASetProperty, sp_OAMethod, and sp_OAGetErrorInfo. These stored procedures are disabled in new installations of SQL Server Database Engine, but enabled in installations upgraded from SQL Server 2000 or SQL Server 7.0.
Service Broker is a new feature in SQL Server 2005 that provides queuing and reliable messaging for Database Engine. Service Broker uses Simple Object Access Protocol (SOAP) messages over an endpoint to communicate with remote applications. Service Broker endpoints can be stopped and disabled to reduce the surface area of SQL Server.
Web Assistant consists of a few stored procedures that generate HTML files from tables of a Database Engine. These stored procedures are sp_makewebtask, sp_dropwebtask, sp_runwebtask, and sp_enumcodepages. The Web Assistant stored procedures are disabled in new installations of SQL Server Database Engine, but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.
Web Assistant will be removed in future releases of SQL Server. If you are planning to use this feature, consider using Reporting Services instead.
xp_cmdshell is a system extended stored procedure that runs a given operating system shell command. By default, xp_cmdshell is disabled in new installations of SQL Server Database Engine but enabled in instances upgraded from SQL Server 2000 or SQL Server 7.0.
SQL Server Agent Stored Procedures
SQL Server Agent is a component of SQL Sever that executes scheduled jobs. SQL Server Agent consists of a service and a number of stored procedures that provide the interface to manage features of the agent. SQL Server Agent stored procedures are enabled and disabled when the service is enabled or disabled.
Enabling and Disabling Analysis Services Features
SQL Server 2005 Analysis Services has a number of features that can be disabled to reduce surface area. By default, these features are disabled on a new installation of Analysis Services, and you should enable these features only if they are needed for your application. To enable or disable these features for an instance of Analysis Services, you can use SQL Server Surface Area Configuration tool (see Figure 3-31).
Figure 3-31. Use the Surface Area Configuration tool to enable or disable Analysis Services features.
Enabling or disabling of Analysis Services features requires the Analysis Services service to be running. In addition, configuring named instances (instances other than the default instance) requires SQL Server Browser service to be running. Viewing features of a named instance of Analysis Services while the SQL Server Browser Service is not running generates the error message shown in Figure 3-32.
Figure 3-32. Connections to named instances of Analysis Services require the SQL Server Browser service to be running.
Ad Hoc Data Mining Queries
Data Mining Extensions (DMX) is a language that allows execution of Data Manipulation Language (DML) and Data Definition Language (DDL) statements against a mining model in Analysis Services. The OPENROWSET function in DMX provides the functionality to use an Object Linking and Embedding Database (OLEDB) provider to execute an ad-hoc data mining query on a remote server. By default, Ad Hoc Data Mining functionality is disabled and should be enabled only if it is needed in your application.
To further control the surface area of the OPENROWSET function, you can enable or disable the passing of the OLEDB provider name and connection string from the Analysis Server Properties dialog in SQL Server Management Studio (see Figure 3-33).
Figure 3-33. Although most of the Analysis Services surface area settings are exposed in the Surface Area Configuration tool, some of the advanced settings can be controlled only from the Server Properties dialog in SQL Server Management Studio.
The Anonymous Connections feature of Analysis Services enables users to connect to Analysis Services with no authentication. The Anonymous Connections feature is disabled by default and it is highly recommended that you keep this feature disabled unless it is absolutely needed by your application.
Analysis Services provides the functionality to link objects such as measure groups and dimensions between two instances of Analysis Services. To enable this functionality, you need to enable one option on each of the instances. On the instance where the linked object is created, you need to enable the Enable links to other instances option, and on the referenced instance you need to enable the Enable links from other instances option. Both these options are disabled by default on new installations of Analysis Services.
Analysis Services provides the functionality to create and run user-defined functions based on Common Language Runtime (CLR) or Component Object Model (COM) objects. By default, execution of CLR and COM-based user-defined functions is disabled, which means you can create or delete this type of user-defined function, but you cannot run the CLR code by accessing these functions.
Enabling and Disabling Reporting Services Features
SQL Server 2005 Reporting Services provides the functionality to configure surface area by disabling or enabling some of its features. By default, these features are disabled on a new installation of Reporting Services. You should enable these features only if they are needed in your applications. To enable or disable these features for a specific instance of Reporting Services, you can use SQL Server Surface Area Configuration (see Figure 3-34):
Figure 3-34. Use the Surface Area Configuration tool to enable or disable Reporting Services features.
Enabling or disabling of Reporting Services features for a particular instance requires the Reporting Services service for that instance to be running. The right pane of the Surface Area Configuration for Features dialog does not display Reporting Services instances that are disabled.
The following sections describe the Reporting Services features that can be enabled or disabled in the Surface Area Configuration tool.
Scheduled Events and Report Delivery
You can reduce the surface area of SQL Server 2005 Reporting Services by disabling its Scheduled Events and Report Delivery feature. Scheduled Events and Report Delivery provides the functionality to schedule operations such as taking report snapshots, delivering reports, and sending cache expiration notifications. You should enable this feature if your implementation of Reporting Services requires report subscription or scheduling report snapshots.
Web Service and HTTP Access
Tools and client applications can use SOAP and URL requests to access a Reporting Server. You can reduce the surface area of Reporting Services by disabling the Web Service and HTTP Access feature. However, disabling this feature also prevents some of the SQL Server tools such as Report Manager, Report Builder, and Management Studio from connecting to the Reporting Services instance.
Windows Integrated Security
A report in Reporting Services accesses and aggregates data from a variety of data sources. With the Windows Integrated Security feature enabled, the credentials of the user running the report are used to access the data. This could create security concerns if user's credentials are used without the user's consent. You can disable the Windows Integrated Security feature and use other methods for accessing report data. These methods include Prompted Credentials and Stored Credentials.
Copying Surface Area Settings between Two Computers
There are situations in which you need to copy all or part of surface area settings from one computer to another. The You may even need to deploy these surface area configuration settings to hundreds or thousands of computers.
The SAC command line utility allows exporting and importing of SQL Server surface area configuration settings to and from a file. These settings include service states, remote connections, and feature states. Here are the steps for copying surface area settings for all SQL Server components from Computer_A to Computer_B:
There are a couple of tips worth noting: SAC exports the feature settings only for components that are running. After exporting and importing the surface area settings, if you notice that certain features are not deployed on your target computer it is most likely because the corresponding components are not running on the source computer. To verify the export operation, you can open the export file in Notepad or Internet Explorer and search for an XML tag named <Features>. If you cannot find the tag, start the component and rerun the command in step 3. Second, execution of the command in step 4 requires remote connections to be enabled on Computer_B. There are two ways to work around this issue. You can run Surface Area Configuration on Computer_A, connect it remotely to Computer_B, enable remote connections on Computer_B, restart the service, and run the command in step 4. Another option is to copy the exported file to Computer_B and run the command in step 4 in a command prompt window on Computer_B.
If you want to deploy the surface area settings to several computers (e.g., Computer_B, Computer_C, Computer_D, ...), you can create a batch file with the command in step 4 for each of the computers. Once again, this assumes remote connections are enabled on the target computers:
sac out sac_settings.xml S Computer_A sac in sac_settings.xml S Computer_B sac in sac_settings.xml S Computer_C sac in sac_settings.xml S Computer_D sac in sac_settings.xml S Computer_E
The commands in steps 3 and 4 copy the surface area settings for all components of SQL Server on the source computer. If you need to deploy a subset of the settings, you can use the SAC command line switches. The SAC command line switches enable you to fine-tune the settings deployed between two computers. For example, if Computer_A has several instances of SQL Server and you want to deploy just the Database Engine features of one instance to Computer_B, you can use the -I, -DE, and -F switches to accomplish this.
sac out sac_DE_mssqlserver_settings.xml S Computer_A I MSSQLSERVER DE F
Remember, the default instance of SQL Server is named MSSQLSERVER.
Stopping SQL Server Services Across Multiple Instances
Consider the situation in which you urgently need to stop all SQL Server services on all computers in your network. One option is to log on to every computer and stop the services manually, but the manual operation will not scale if you have to stop tens or hundreds of instances of SQL Server.
Notice that the export file also includes the services startup mode, which is applied to the target computers upon import of the file. As part of step 1, you need to make sure the service startup modes are set the same as those on the target computers.
Configuring Surface Area of a Remote Computer
Both the Surface Area Configuration tool and SAC command line utility provide the functionality to configure the surface area of a SQL Server on a remote computer. To connect SAC to a remote computer, you can use its command line S switch. To connect Surface Area Configuration to a remote computer, click on the link change computer on its main dialog (see Figure 3-35).
Figure 3-35. Surface Area Configuration can connect to a remote computer.
Clicking on change computer launches the Select Computer dialog, where you can enter the name of a remote computer or select the option to connect to the local computer (see Figure 3-36).
Figure 3-36. The Select Computer dialog.
The name of the remote computer is displayed back on the main Surface Area Configuration dialog (see Figure 3-37). This is useful for situations in which you need to quickly figure out the name of the computer to which Surface Area Configuration is connected.
Figure 3-37. Surface Area Configuration can connect to a remote computer.
Now, if you launch the Surface Area Configuration for Services and Connections or Surface Area Configuration for Features dialogs, they will be launched in the context of the remote computer. The name of the remote computer is also displayed in the title bar of these dialogs.
Sending Information About Feature Usage and Serious Errors to Microsoft
SQL Server 2005 provides functionality to send information about feature usage and serious system errors to Microsoft. This information helps the SQL Server team to improve the product by fixing the most common bugs and gives them a better understanding of product usage scenarios. You can choose to opt in or out of each of these options for all instances on your computer or for a specific component.
By default, SQL Server error and usage information is collected once per day at 12:00 a.m. If 12:00 a.m. is not a suitable time for your server, you can change the time of collection by manually editing the Registry key that controls the collection time. There is a Registry key for each SQL Server instance identified by its instance ID. For example, the following is the Registry key with instance ID of MSSQL.1.
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\CPE\ TimeofReporting
The value of the Registry key is the number of minutes from 12:00 a.m. when the information will be sent to Microsoft. For example, a value of 30 would set the collection at 12:30 a.m., and value of 600 would run the collection at 10:00 a.m.
The information that is sent to Microsoft is classified in two categories: Error Reporting and Feature Usage. These two options can be found in the Error and Usage Report Settings dialog. As you can see in Figure 3-38, the labels for these two options are somewhat wordier than what we have been calling them. This happened after the labels were reviewed by the Microsoft legal team.
Figure 3-38. The simple view of Error and Usage Report Settings provides the functionality to opt in or out of sending information for all components.
The Error and Usage Report Settings dialog (see Figure 3-38) has two modes: Simple and Advanced. In the simple mode, you can choose to opt in or out of error and usage reporting for all components in all instances of SQL Server.
The advanced mode of the Error and Usage Report Settings dialog (see Figure 3-39) provides a more granular control of components that can send error and usage reports to Microsoft. On the Error and Usage Report Settings dialog, click on Options to view the error and usage settings for every component in every instance.
Figure 3-39. The advanced view of Error and Usage Report Settings provides the functionality to opt in or out of sending information for a specific component.
At first glance, you may find the information in the grid rather confusing. However, as soon as you understand the rules applied to classify the information, you will likely find it intuitive. There is a row for each of the instance-aware components such as Database Engine, Analysis Services, and Reporting Services. Other components are presented as Others in All Instances. Also, unlike other tools, the default instance is displayed as <Default>.