When you start SQL Server 2005 Surface Area Configuration, you will see the main window shown in Figure 3-1. SQL Server 2005 Surface Area Configuration can be used to perform several main tasks:
Connect to a specific SQL Server installation
Manage the services configuration of a specified server
Manage the connections configuration of a specified server
Manage features of various SQL Server components
When you start SQL Server 2005 Surface Area Configuration, you are connected to the local computer by default. Once you have accessed the tool and are working with it, you can change the computer you are working with by clicking the Change Computer link provided in the interface to display the Select Computer dialog box, as shown in Figure 3-2. If you want to manage the configuration of the computer the tool is running on, select Local Computer and then click OK. If you want to manage the configuration of a remote computer, select Remote Computer, type the name of the remote computer, such as DBSvr05, and then click OK.
You can use SQL Server 2005 Surface Area Configuration to view and manage the startup state of SQL Server services. Start SQL Server 2005 Surface Area Configuration, and then click the Surface Area Configuration For Services And Connections link provided in the main interface. The tool will examine the configuration of services and connections for all running instances of SQL Server 2005 on the host to which you are currently connected. When the examination is complete, you can use the tabs provided to manage services and connections by instance or by component (see Figure 3-3). The components available depend on what you have installed and include:
Database Engine. Runs as the SQL Server (InstanceName) service. The executable file for this service is Sqlservr.exe, and the service runs under a specific instance specified in the startup command line, such as (for the default instance, MSSQLSERVER):
"C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe" -sMSSQLSERVER
Note
Although some components, like the database engine, can be started directly from the command-line, services typically are started with the appropriate tool or with NET START. If you manually start up the database engine, you can set specific startup parameters as discussed in Chapter 4. You can also set startup parameters using the Services utility. In Services, double-click the SQL Server service for the instance with which you want to work. In the Properties dialog box, click Stop to stop the service. Enter the startup parameters in the Start Parameters field, and then click Start to start the service.
Analysis Services. Runs as the Analysis Services (InstanceName) service. The executable file for this service is Msmdsrv.exe, and the service runs a specific initialization file specified in the startup command line, such as:
"C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPinmsmdsrv.exe" -s "C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPConfigmsmdsrv.ini"The initialization file (Msmdsrv.ini) is defined using XML and should not be edited directly.
Reporting Services. Runs as the Report Server (InstanceName) service. The executable file for this service is ReportingServicesService.exe, specified with the service startup command line, such as:
"C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerinReportingServicesService.exe"
SQL Server Agent. Runs as the SQL Server Agent (InstanceName) service. The executable file for this service is Sqlagent90.exe, and the service runs under a specific instance specified in the startup command line, such as:
"C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSQLAGENT90.EXE" -i MSSQLSERVERFull-Text Search. Runs as the Msftesql service. The executable file for this service is Msftesql.exe, and the service runs from a specified startup folder under a specific instance specified in the startup command line, such as:
"C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnmsftesql.exe" -s:MSSQL.1 -f:MSSQLSERVERIntegration Services. Runs as the DTS Server service. The executable file for this service is Msdtssrvr.exe, specified with the service startup command line, such as:
"C:Program FilesMicrosoft SQL Server90DTSBinnMsDtsSrvr.exe"
SQL Server Browser. Runs as the SQL Browser service. The executable file for this service is Sqlbrowser.exe, specified with the service startup command line, such as:
"C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe"
To view the startup state of a service, expand the appropriate component node(s) for the SQL Server instance you want to work with. Once you have selected a component entry or a service entry, you will see a detailed entry for the service that includes the following information:
Service Name. The internal name for the service used by the operating system
Display Name. The common name for the service shown in the user interface
Description. The related SQL Server 2005 component
Startup Type. The startup state of the service—Automatic, Manual, or Disabled
Service Status. The status of the service as of the last refresh, such as Running or Stopped
Any SQL Server services not being used or not required for your installation should be set to manual startup and stopped if they are running. To change the startup type or the service, select the startup type to use, and then click Apply. To stop a running service, click Stop. If you want to prevent a service from running, select Disabled as the Startup Type. Keep in mind that the SQL Server Browser service provides connection information to client computers. If clients connect to SQL Server remotely, this service is required (in most instances).
Note
You can also use the Services utility and SQL Server Configuration Manager to manage SQL Server services. With the Services utility, you manage SQL Server services as you would any other service. With SQL Server Configuration Manager, you can manage the service logon account, the startup type, and status. If applicable, you can also manage advanced features such as dump directory, error reporting, and startup parameters. The advantage of SQL Server 2005 Surface Area Configuration and SQL Server Configuration Manager over the Services utility is that they streamline the information and only provide access to SQL Server services rather than all system services.
SQL Server installations can be configured to provide local, remote, and dedicated connections. Local connections are used by applications running on the computer that is also running SQL Server. Remote connections are used by clients connecting to the server, by applications running on other servers, and by other SQL servers. Dedicated connections are a special feature used by administrators to maintain SQL Server installations (and managed as a configurable feature rather than a permissible connection type).
Note
The default configuration for connections depends on how you have configured service accounts, what components are installed, and other installation options, such as whether you performed an upgrade or new installation. Typically, a new installation will be configured for local connections only. However, if you have installed additional components, such as Report Services or Notification Services, the configuration will usually permit local and remote connections.
Although a configuration for only local connections provides obvious security advantages, you cannot always run SQL Servers in this configuration. Often, and more typically, you will need to allow incoming connections from remote clients and servers, and in this case, the permitted connection protocols can affect the amount of resources used and the relative security of the server. For remote connections, SQL Server 2005 can use TCP/IP, Named Pipes, or both. Because TCP/IP and Named Pipes require specific and different ports to be open across a firewall, you can limit the server to one protocol or the other to reduce the potential attack surface. Before you change the permissible connection types, however, you should make sure that all clients and applications are configured to use the appropriate network library.
Tip
With TCP/IP, SQL Server can communicate using standard IP and the TCP/IP Sockets Net-Library. The default listen port for the default instance is TCP port 1433. The default listen port for named instances is set dynamically, unless otherwise assigned. TCP port 1434 is used for client connections. When you use named pipes, SQL Server 2005 uses the Named Pipes Net-Library to communicate over a standard network address: \.pipesqlquery for the default instance and \.pipeMSSQL$instancenamesqlquery for a named instance. Named pipes require a range of ports to be open for communication across a firewall. With named pipes, the server will listen on TCP port 445; NetBIOS name lookups are done on UDP port 139. With b-node broadcasts, NetBIOS name resolution requires UDP ports 137 and 138, or you can use a WINS server or LMHOSTS files.
SQL Server 2005 also supports Shared Memory protocol for local connections and Virtual Interface Architecture (VIA) for both local and remote connections. NWLink IPX/SPX and AppleTalk are no longer supported.
You can check and change the connection configuration using the following steps:
Start SQL Server 2005 Surface Area Configuration and then click the Surface Area Configuration For Services And Connections link provided in the main interface.
Note
SQL Server 2005 Surface Area Configuration examines the configuration of all running instances of SQL Server 2005 on the host to which you are currently connected. If you have stopped a SQL Server instance, you must start the instance to be able to manage it using this tool. You may need to close the current window and reopen it as well.
Expand the node for the SQL Server instance you want to work with, such as the default instance MSSQLSERVER.
Expand the Database Engine node and then select Remote Connections from the View By Instance tab, as shown in Figure 3-4.
If remote clients, applications, and servers do not need to connect to the server, select Local Connections Only in the main interface. Otherwise, select the Local And Remote Connections option and then specify the permitted types of connections. The options are:
Using TCP/IP Only
Using Named Pipes Only
Using both TCP/IP and Named Pipes
Click Apply.
To reduce the server surface area and improve server security, you should enable only the features needed by your clients and applications. This will limit the ways the server can be exploited by malicious users and close avenues of potential attack. Although you can manage surface area features through configuration parameters in SQL Server Management Studio and through stored procedures, the easiest way to manage surface area features is to use SQL Server 2005 Surface Area Configuration. This tool lets you manage all running instances of the SQL Server Database Engine, Analysis Services, and Reporting Services.
Tip
In a standard installation, most surface area features are disabled by default to enhance security.
You can check and manage surface area features using the following steps:
Start SQL Server 2005 Surface Area Configuration and then click the Surface Area Configuration For Features link provided in the main interface.
Note
SQL Server 2005 Surface Area Configuration examines the configuration of all running instances of SQL Server 2005 on the host to which you are currently connected. If you have stopped a SQL Server instance, you must start the instance to be able to manage it using this tool. You may need to close the current window and reopen it as well.
Expand the node for the component instance you want to work with, such as the default instance MSSQLSERVER.
Table 3-1 details the surface area features you can manage for the SQL Server Database Engine, Analysis Services, and Reporting Services. Select the feature you want to manage, as shown in Figure 3-5.
Enable the feature by selecting the check box provided. Alternately, you can disable the feature by clearing the check box if it has been selected.
Click Apply.
Table 3-1. Component Features for Managing Surface Area Access
Component/Feature | Description/Usage |
|---|---|
Database Engine | |
Ad Hoc Remote Queries | The OPENROWSET and OPENDATASOURCE functions can use ad hocconnections to work with remote data sources without an administrator specifically configuring linked or remote servers.If your applications or scripts use these functions, you should enable OPENROWSET and OPENDATASOURCE support. Otherwise, this feature should be disabled. |
CLRIntegration | WithCommon Language Runtime (CLR) Integration, you can write storedprocedures, triggers, user-defined types, and user-defined functions using VB.NET, C#, and any other .NET framework language. If your applications or scripts use .NET framework languages, enable this feature. Otherwise, this feature should be disabled. |
DatabaseMail | DatabaseMail replaces SQL Mail as the preferred technique for sending e-mail messages from SQL Server using Simple Mail Transfer Protocol (SMTP). Enable this feature if you have created a mail host database (by running the %ProgramFiles%Microsoft SQLServerMSSQL.1MSSQLInstallInstall_DBMail_Upgrade.sql script on the server) and the necessary database mail profiles,and you want applications and scripts to be able to use the sp_send_dbmail stored procedure to send e-mail messages from SQL Server. Otherwise, this feature should be disabled. |
DAC | Using the SQLCMD command-line utility with the —A parameter,administrators can maintain SQL Server installations using a dedicated connection from the command line, either locally or remotely. By default, only local dedicated connections are permitted. If you want to authorize remote dedicated connections, enable this feature. Otherwise, this feature should be disabled. |
With Native Web Services, you can access SQL Server over HTTP using Simple Object Access Protocol (SOAP) messaging. SOAP messages contain text-based commands that are formatted with XML. If you plan to use SOAP for data exchange and have configured the necessary HTTP endpoints, you can configure the state of each endpoint as Started, Stopped, or Disabled. If no HTTP endpoints have been defined, you will not be able to configure or manage this feature. It is important to note that the Report Server Web service, SQL Server Service Broker, and Database Mirroring components make use of the Native Web Services, but they have separate configurations. | |
OLE Automation | OLE Automation provides the ability to use Transact-SQL batches,stored procedures, and triggers to reference SQL DMO and custom OLE Automation objects. Enable this feature if you want to beable to use OLE Automation, including the extended stored procedures sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo,sp_OAGetProperty, sp_OAMethod, sp_OASetProperty, andsp_OAStop. Otherwise, this feature should be disabled. |
Service Broker | Service Broker provides queuing and messaging for the database engine.Applications can use the Service Broker to communicate across instances of SQL Server. If your applications use Service Broker and you have configured the necessary HTTP endpoints, you can configure the state of each endpoint as Started, Stopped, orDisabled. If no HTTP endpoints have been defined, you will not be able to configure or manage this feature. |
SQL Mail | SQLMail can be used with legacy applications for sending e-mail messages from SQL Server using SMTP. Enable this feature if you want legacy applications and scripts to be able to use the xp_send stored procedure to send e-mail messages from SQLServer. Otherwise, this feature should be disabled. |
Web Assistant | In previous versions of SQL Server, Web Assistant stored procedures could be used to generate HTML files from SQL Server data. InSQL Server 2005, Reporting Services takes the place of these stored procedures because this feature is more robust and has more configuration options. If you have legacy applications or scripts that use Web Assistant, enable this feature. Otherwise,this feature should be disabled. |
xp_cmdshell | The xp_cmdshell executes command strings using the operating system command shell and returns the results as rows of text. If you want applications and scripts to run operating system commands, you must enable this feature. By default, only members of the sysadmin fixed server role can execute xp_cmdshell.You can grant execution permission to other users. For sysadmin users, xp_cmdshell is executed under the security context in which the SQL Server service is running. For other users, xp_cmdshell will impersonate the SQL Server Agent proxy account (as specified using xp_sqlagent_proxy_account).If the proxy account is not available, xp_cmdshell willfail. |
Ad Hoc Data Mining Queries | The Data Mining Extensions OPENROWSET function establishes a connection to a data source object by using a provider name and connection string. This permits ad hoc connections to remote data sources without an administrator specifically configuring linked or remote servers. Enable this feature if your applications or scripts use OPENROWSET with Data Mining.Otherwise, this feature should be disabled to prevent applications and scripts from passing a provider name and connection string when using the OPENROWSET function. |
Anonymous Connections | With anonymous connections, unauthenticated users can establish connections with Analysis Services. Enable this feature if your applications and scripts require unauthenticated user access.Otherwise, disable this feature. |
Linked Objects | With Analysis Services, you can use linked objects to link dimensions and measure groups between servers. If you want Analysis Server to link to other servers, select Enable Links To Other Instances. If you want Analysis Server to be linked from other servers, select Enable Links From Other Instances. If dimension and measure group linking are not used, clear both check boxes to disable this feature. |
User-Defined Functions | Analysis services is integrated with the .NET framework and can loadassemblies containing user-defined functions. These functions can be written using the CLR or component object model (COM)objects. CLR objects and functions have an integrated security model. COM objects do not use this model, and they are therefore less secure inherently. Enable this feature if your applications and scripts require user-defined COM functions.Otherwise, disable this feature to permit only CLR functions. |
Reporting Services | |
Scheduled Events and Report Delivery | With Report Services, you can use ad hoc, on-demand reports and scheduled reports. Typically, when you have installed Report Services, both types of reports are enabled. If you do not use scheduled reports, you can disable this aspect of report generation and delivery by clearing the Enable Scheduled Events And Report Delivery check box. |
HTTP and Web Service Requests | Report Services components use SOAP messaging over HTTP for communications and HTTP for URL access requests. These features are handled by the Report Server Web Service and permit you to work with Report Services through Report Manager, Report Builder, and SQL Server Management Studio. Typically, if Report Services are installed, the server will handle HTTP and Web Service requests. If you do not use Reporting Services, disable this feature by clearing the Enable Web Service And URL Access check box. |