SQL Server Configuration Manager Help
SQL Server Configuration Manager Help |
 | See Also

Use SQL Server Configuration Manager to configure SQL Server services and configure network connectivity. To create or manage database objects, configure security, and write Transact-SQL queries, use SQL Server Management Studio. For SQL Server Express, use the Express Manager tool. For more information about SQL Server Management Studio and Express Manager, see Managing SQL Server 2000 Services.

This section contains the F1 Help topics for the dialogs in SQL Server Configuration Manager.

Note:
SQL Server Configuration Manager cannot configure versions of SQL Server earlier than Microsoft SQL Server 2005.

Services

SQL Server Configuration Manager manages services that are related to SQL Server. Although many of these tasks can be accomplished using the Microsoft Windows Services dialog, is important to note that SQL Server Configuration Manager performs additional operations on the services it manages, such as applying the correct permissions when the service account is changed. Using the normal Windows Services dialog to configure any of the SQL Server services might cause the service to malfunction.

Use SQL Server Configuration Manager for the following tasks for services:

  • Start, stop, and pause services

  • Configure services to start automatically or manually, disable the services, or change other service settings

  • Change the passwords for the accounts used by the SQL Server services

  • Start SQL Server using trace flags (command line parameters)

  • View the properties of services

SQL Server Network Configuration

Use SQL Server Configuration Manager for the following tasks related to the SQL Server services on this computer:

  • Enable or disable a SQL Server network protocol

  • Configure a SQL Server network protocol

Note:
For a short tutorial about how to configure protocols and connect to the SQL Server Database Engine, see Tutorial: Getting Started with the Database Engine.

SQL Server Native Client Configuration

SQL Server clients connect to SQL Server by using the SQL Server Native Client network library. Use SQL Server Configuration Manager for the following tasks related to client applications on this computer:

  • For SQL Server client applications on this computer, specify the protocol order, when connecting to instances of SQL Server.

  • Configure client connection protocols.

  • For SQL Server client applications, create aliases for instances of SQL Server, so that clients can connect using a custom connection string.

For more information about each of these tasks, see F1 help for each task.

To open SQL Server Configuration Manager

  • On the Start menu, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Configuration Manager.

See Also

Reference

SQL Server 2008 Services
SQL Server 2008 Network Configuration
SQL Server Native Client Configuration
Managing SQL Server 2000 Services

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


SQL Server 2008 Services

SQL Server 2008 Services |
 | See Also
SQL Server Configuration Manager Help >

This section contains F1 Help topics for the SQL Server Services dialog boxes in Microsoft SQL Server Configuration Manager.

Note:
If you open SQL Server Configuration Manager on a node of a cluster that does not host the resources, the services show as stopped.

Note:
If the nodes of the SQL Server Services do not populate with the SQL Server services available on the computer, you may not have sufficient permissions to access Windows Management Instrumentation (WMI). To configure permissions on WMI, search Books Online for the topic, "How to: Configure WMI to Show Server Status in SQL Server Tools."

See Also

Concepts

SQL Server Configuration Manager Help

© 2010 Microsoft Corporation. All rights reserved.


Analysis Server <server> (SQL Server Configuration Manager)

Analysis Server <server> (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports Microsoft Analysis Services. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For information about Analysis Services, see Books Online.


Analysis Server Properties (Log On Tab)

Analysis Server Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the Analysis Server Properties dialog box to specify the account used by the SSAS service, and to start and stop the service.

Note:
When changing the Account Name used by a service on a clustered instance, the new account must either be a member of the domain group specified during setup for the service being changed, or you must have permission to add members to that group. If you do not have permission to modify group membership, contact the domain administrator.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may restrict the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Microsoft Windows Authentication. Microsoft recommends using a domain user account with minimal rights for services. For information about selecting an account, search Books Online for the topic Setting Up Windows Service Accounts.

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


Analysis Server Properties (Service Tab)

Analysis Server Properties (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft Analysis Services. This service must be running for SSAS to work properly. The property values in light gray cannot be changed using this application.

Options

Binary Path

Displays the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running SSAS.

Name

Indicates the display name of the service.

Process ID

Displays the number used by Microsoft Windows to keep track of this program's processes.

SQL Service Type

Displays the type of service provided to calling processes. Microsoft SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled.


Analysis Server Properties (Advanced Tab)

Analysis Server Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft Analysis Services. If custom properties are defined, they appear on this tab, with their values.

Options

Clustered

Indicates whether this service is installed as a resource of a clustered server.

Customer Feedback Reporting

Indicates whether Service Quality Monitoring has been enabled on this service. For more information on Customer Feedback Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Dump Directory

Displays the location where memory dumps are placed in case of an error.

Error Reporting

When set to Yes, the Dr. Watson program forwards information to either Microsoft or your error server if a serious failure occurs. For more information on Error Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Instance ID

Indicates the instance that uses this service.


Notification Services (SQL Server Configuration Manager)

Notification Services (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports Notification Services. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For information about Notification Services, see Books Online.


NS$<service name> Properties (Log On Tab)

NS$<service name> Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the Notification Services Properties dialog box to specify the account used by the Notification Services service, and to start and stop the service.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may restrict the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Microsoft Windows Authentication. Microsoft recommends using a domain user account with minimal rights for services. For information about selecting an account, search Books Online for the topic, "Setting Up Windows Service Accounts."

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


NS$<service name> Properties (Service Tab)

NS$<service name> Properties (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft Notification Services service. The property values in light gray cannot be changed using this application.

Options

Binary Path

Displays the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running the full text search.

Name

Indicates the display name of the service.

Process ID

Displays the Microsoft Windows process ID.

SQL Service Type

Type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled.


NS$<service name> Properties (Advanced Tab)

NS$<service name> Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Notification Services is implemented as a service named in the format NS$<user_configured name>. If custom properties are defined, they appear on this tab, with their values. This tab may have no properties listed.


Report Server <server> (SQL Server Configuration Manager)

Report Server <server> (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports Microsoft Reporting Services. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For information about Reporting Services, see Books Online.


Report Server Properties (Log On Tab)

Report Server Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the Report Server Properties dialog box to specify the account used by the Report Server service, and to start and stop the service.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may restrict the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Microsoft Windows Authentication. Microsoft recommends using a domain user account with minimal rights for services. For information about selecting an account, search Books Online for the topic Setting Up Windows Service Accounts.

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


Report Server Properties (Service Tab)

Report Server Properties (Service Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft SQL Server Report Server service. The property values in light gray cannot be changed by using this application.

Options

Binary Path

Displays the location of the program files used by this service.

Error Control

1 indicates "SERVICE_ERROR_NORMAL". If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running the full text search.

Name

Indicates the display name of the service.

Process ID

Displays the Microsoft Windows process ID.

SQL Service Type

Type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled.

See Also

Reference

SQL Server 2008 Services

© 2010 Microsoft Corporation. All rights reserved.


Report Server Properties (Advanced Tab)

Report Server Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft Reporting Services. If custom properties are defined, they appear on this tab, with their values.

Options

Customer Feedback Reporting

Indicates whether Service Quality Monitoring has been enabled on this service. For more information on Customer Feedback Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Dump Directory

Displays the location where memory dumps are placed in case of an error.

Error Reporting

When set to Yes, the Dr. Watson program forwards information to either Microsoft or your error server if a serious failure occurs. For more information on Error Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Instance ID

Indicates the instance that uses this service.


SQL Server Agent <server> (SQL Server Configuration Manager)

SQL Server Agent <server> (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports SQL Server Agent. To view the properties of the service, right-click the service in the details pane, and then click Properties.

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. For more information about SQL Server Agent, see "About SQL Server Agent" in SQL Server Books Online.


SQL Server Agent Properties (Log On Tab)

SQL Server Agent Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the SQL Server Agent Properties dialog box to specify the account used by the SQL Server Agent service, and to start and stop the service. Changing the password of an account takes effect immediately without restarting the service.

Note:
When changing the account name used by a service on a clustered instance, the new account must be a member of the domain group specified during setup for the service being changed, or you must have permission to add members to that group. If you do not have permission to modify group membership, contact the domain administrator.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may restrict the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Windows Authentication. Microsoft recommends using a domain user account with minimal rights for services. For information about selecting an account, search Books Online for "Setting Up Windows Service Accounts."

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


SQL Server Agent Properties (Advanced Tab)

SQL Server Agent Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Five properties appear on the Advanced tab by default. If custom properties are defined, they also appear on this tab, with their values.

Options

Clustered

Indicates if this service is installed as a resource of a clustered server.

Customer Feedback Reporting

Indicates whether Service Quality Monitoring has been enabled on this service. For more information on Customer Feedback Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Dump Directory

Displays the location where memory dumps are placed in case of an error.

Error Reporting

When set to Yes, the Dr. Watson program forwards information to either Microsoft or your error server if a serious failure occurs. For more information on Error Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Instance ID

Indicates the instance that uses this service.


SQL Server Agent Properties (Service Tab)

SQL Server Agent Properties (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This service is the Microsoft SQL Server Agent service. The property values in light gray cannot be changed using this application.

Options

Binary Path

Displays the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running SQL Server Agent.

Name

Indicates the display name of the service.

Process ID

Displays the Microsoft Windows process ID.

SQL Service Type

Displays the type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.


SQL Server Browser (SQL Server Configuration Manager)

SQL Server Browser (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports SQL Server Browser. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For a description of the SQL Server Browser service and how it works, see SQL Server Browser Service.


SQL Server Browser Properties (Log On Tab)

SQL Server Browser Properties (Log On Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Services >

The SQL Server Browser program runs as a service on the server. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

SQL Server Browser listens on a UDP port and accepts unauthenticated requests using SQL Server Resolution Protocol (SSRP).

Changing the password of an account takes effect immediately without restarting the service.

Options

Local System account

Run the SQL Server Browser service in the security context of the Local System account. When possible, use a low-permission account instead.

This account

Specify a local or domain user account that uses Windows Authentication. We recommend using a domain user account with minimal rights for services. For information about selecting an account, see "Setting Up Windows Service Accounts" in SQL Server Books Online.

Browse

Browse for a user or built-in security principal.

Important:
Use a low-permission account. For information about the permissions required for the SQL Server Browser Service, see the Security section of SQL Server Browser Service.

Password

Enter the password of the security principal.

Confirm password

Confirm the password of the security principal.

Service status

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.

Start

Start the SQL Server Browser service.

Stop

Stop the SQL Server Browser service.

Pause

Pause the SQL Server Browser service.

Resume

Resume a paused SQL Server Browser service.

See Also

Concepts

SQL Server Browser Service

© 2010 Microsoft Corporation. All rights reserved.


SQL Server Browser Properties (Service Tab)

SQL Server Browser Properties (Service Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Services >

The SQL Server Browser program runs as a service on the server. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

Use the Service tab on the SQL Server Browser Properties dialog box to view the following options. All properties except Start Mode are read-only.

Options

Binary Path

Displays the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running the SQL Server Browser service.

Name

Indicates the display name of the service.

Process ID

Displays the Windows process ID.

Service Type

Displays the type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.

See Also

Concepts

SQL Server Browser Service

© 2010 Microsoft Corporation. All rights reserved.


SQL Server Browser Properties (Advanced Tab)

SQL Server Browser Properties (Advanced Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Services >

The SQL Server Browser program runs as a service on the server. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

Options

Clustered

Indicates if this service is installed as a resource of a clustered server.

Customer Feedback Reporting

Indicates whether Service Quality Monitoring has been enabled on this service. For more information on Customer Feedback Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Dump Directory

The location where memory dumps are placed in case of an error.

Error Reporting

When set to Yes, the Dr. Watson program forwards information to either Microsoft or your error server if a serious failure occurs. For more information on Error Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Instance ID

Indicates the SQL Server instance that used this SQL Server Agent instance. The default instance is MSSQL10_50.MSSQLSERVER.

See Also

Concepts

SQL Server Browser Service

© 2010 Microsoft Corporation. All rights reserved.


SQL Server <server> (SQL Server Configuration Manager)

SQL Server <server> (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports Microsoft SQL Server Database Engine. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For information about the SQL Server Database Engine, see Books Online.


SQL Server Properties (Log On Tab)

SQL Server Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the SQL Server Properties dialog box to specify the account used by the SQL Server service, to change the password of an account, and to start and stop the service. Changing the password of an account takes effect immediately.

Note:
When changing the account name used by a service on a clustered instance, the new account must be a member of the domain group specified during setup for the service being changed, or you must have permission to add members to that group. If you do not have permission to modify group membership, contact the domain administrator.

For more information about selecting an account to run the service, see "Setting Up Windows Service Accounts" in SQL Server Books Online.

Options

Built-in account
Local System
  • Specify the local system account. This account does not require a password. However, the local system account may prevent the service from interacting with other servers, depending on the privileges granted to the account.

Local Service
  • Specify the local service account. This account does not require a password. However, the local service account may prevent the service from interacting with other servers, depending on the privileges granted to the account.

Network Service
  • We recommend that you do not use the Network Service account for the SQL Server or the SQL Server Agent services. Local User or Domain User accounts are more appropriate for these services.

This account

Specify a local or domain user account that uses Windows Authentication. We recommend that you use a domain user account that has minimal rights for services.

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.

Note:
When starting SQL Server, a WMI error containing the phrase "not implemented [0x80004001]" may indicate that SQL Server is not installed on the target computer.


SQL Server Properties (Service Tab)

SQL Server Properties (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Service tab on the MSSQLSERVER Properties dialog box to view or specify the following options.

Options

Binary Path

Lists the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running the Microsoft SQL Server service.

Name

Indicates the display name of the service.

Process ID

Displays the Windows process ID.

Service Type

Displays the type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.


SQL Server Properties (FILESTREAM Tab)

SQL Server Properties (FILESTREAM Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use this page to enable FILESTREAM for this installation of SQL Server 2008.

Enable FILESTREAM for Transact-SQL access

Select to enable FILESTREAM for Transact-SQL access. This control must be checked before the other control options will be available.

Enable FILESTREAM for file I/O streaming access

Select to enable Win32 streaming access for FILESTREAM.

Windows share name

Use this control to enter the name of the Windows share in which the FILESTREAM data will be stored.

Allow remote clients to have streaming access to FILESTREAM data

Select this control to allow remote clients to access this FILESTREAM data on this server.


SQL Server Properties (Advanced Tab)

SQL Server Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

The following properties appear on the Advanced tab by default. If custom properties are defined, they also appear on this tab, with their values.

Options

Clustered

Indicates if this service is installed as a resource of a clustered server.

Customer Feedback Reporting

Indicates whether Service Quality Monitoring has been enabled on this service. For more information on Customer Feedback Reporting, search Books Online for the topic, "Error and Usage Report Settings."

Data Path

Displays the path to the SQL Server binaries for this installation of SQL Server.

Dump Directory

Displays the location where memory dumps are placed in case of an error.

Error Reporting

When set to Yes, the Dr. Watson program forwards information to either Microsoft or your error server if a serious failure occurs. For more information on Error Reporting, search Books Online for the topic, "Error and Usage Report Settings." To change this value, in SQL Server Management Studio Object Explorer, right-click your server, click Properties, and then click the Misc. Server Settings page. The options are presented in the Information Reporting area.

File Version

Displays the version of the SQL Server executable.

Install Path

Displays the path to the SQL Server binaries for this installation of SQL Server.

Instance ID

Indicates the SQL Server instance that used this service.

Language

Displays the default language for server messages.

Registry Root

Displays the location of the registry keys used by this application.

Service Pack Level

Displays the service pack level of this instance of SQL Server.

SKU Name

Displays the product stock keeping unit (SKU), sometimes called the product edition.

Startup Parameters

Lists any startup parameters that are used by this instance of SQL Server. Parameters are separated by semi-colons. The default parameters include the paths to the data file for the master database (master.mdf), the log file for the master database (mastlog.ldf), and the error log file. For the syntax of startup parameters, search Books Online for the topic Using the SQL Server Service Startup Options.

Stock Keeping Unit

Displays the product stock keeping unit (SKU) number.

Version

Displays the version number of this instance of SQL Server.

Virtual Server Name

Virtual Server Name when SQL Server is installed on a clustered server.


SQL Server Integration Services 10.0 (SQL Server Configuration Manager)

SQL Server Integration Services 10.0 (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

This is the service that supports SSIS. To view the properties of the service, right-click the service in the details pane, and then click Properties.

For information about SSIS, see Books Online.


SQL Server Integration Services Properties (Log On Tab)

SQL Server Integration Services Properties (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Log On tab of the Integration Services Properties dialog box to specify the account used by the Integration Services service, and to start and stop the service.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may restrict the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Microsoft Windows Authentication. Microsoft recommends using a domain user account with minimal rights for services. For information about selecting an account, search Books Online for the topic, "Setting Up Windows Service Accounts."

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


SQL Server Integration Services Properties (Service Tab)

SQL Server Integration Services Properties (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Use the Service tab on the Integration Services Properties dialog box to view or specify the following options.

Options

Binary Path

Displays the the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

The Microsoft Windows error code defining any problems encountered in starting or stopping the service. This property is set to ERROR_SERVICE_SPECIFIC_ERROR (1066) when the error is unique to the service represented by this class, and information about the error is available in the ServiceSpecificExitCode property. The service sets this value to NO_ERROR (0) when running, and again upon normal termination.

Host Name

Displays the name of the computer or cluster running the SSIS service.

Name

Indicates the display name of the service.

Process ID

Displays the Windows process ID.

SQL Service Type

Displays the type of service provided to calling processes. Microsoft SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.


SQL Server Integration Services Properties (Advanced Tab)

SQL Server Integration Services Properties (Advanced Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Microsoft Integration Services is implemented as the Integration Servicesservice. If custom properties are defined, they appear on this tab, with their values. This tab may have no properties listed.


SQL Full-text Filter Daemon Launcher (SQL Server Configuration Manager)

SQL Full-text Filter Daemon Launcher (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Beginning in SQL Server 2008, the SQL Full-text Filter Daemon Launcher (FDHOST Launcher) service is used by SQL Server full-text search to start the filter daemon host process, which handles full-text search filtering and word breaking. This service must be running to use full-text search. The FDHOST Launcher service is an instance-aware service that is associated with a specific instance of SQL Server. The FDHOST Launcher service propagates the service account information to each filter daemon host process started. For information about the filter daemon host processes, see "Full-Text Search Architecture" in SQL Server Books Online.


SQL Full-text Filter Daemon Launcher (Log On Tab)

SQL Full-text Filter Daemon Launcher (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Beginning in SQL Server 2008, the SQL Full-text Filter Daemon Launcher (FDHOST Launcher) service is used by SQL Server full-text search. This service must be running if you use full-text search. For information about the filter daemon host processes, see "Full-Text Search Architecture" in SQL Server Books Online.

Use the Log On tab of the SQL Full-text Filter Daemon Launcher Properties dialog box to specify the account used by the SQL Server full-text service, to change the password of an account, and to start and stop the service. Changing the password of an account takes affect after restarting the service.

Note:
When changing the account name used by a service on a clustered instance, either the new account must be a member of the domain group specified during setup for the service, or you must have permission to add members to that group. If you do not have permission to modify group membership, contact the domain administrator.

For more information about selecting an account to run the service, see "Setting Up Windows Service Accounts" in SQL Server Books Online.

Options

Built-in account
Local System

Specify the local system account. This account does not require a password. However, the local system account may prevent the service from interacting with other servers, depending on the privileges granted to the account.

Local Service

Specify the local service account. This account does not require a password. However, the local service account may prevent the service from interacting with other servers, depending on the privileges granted to the account.

Network Service

We recommend that you do not use the Network Service account for the SQL Server services or the SQL Server Agent services. Local User or Domain User accounts are more appropriate for these services.

This account

Specify a local or domain user account that uses Windows Authentication. We recommend that you use a domain user account that has minimal rights for services.

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service. Not available for this service.

Resume

Resume a paused service.


SQL Full-text Filter Daemon Launcher (Service Tab)

SQL Full-text Filter Daemon Launcher (Service Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Beginning in SQL Server 2008, the SQL Full-text Filter Daemon Launcher (FDHOST Launcher) service is used by SQL Server full-text. This service must be running if you use full-text search. For information about the filter daemon host processes, see "Full-Text Search Architecture" in SQL Server Books Online.

Use the Service tab on the SQL Full-text Filter Daemon Launcher Properties dialog box to view or specify the following options.

Options

Binary Path

Lists the location of the program files used by this service.

Error Control

1 indicates SERVICE_ERROR_NORMAL. If the service fails to start during computer start up, the startup program logs the error and displays a pop-up message box but continues the startup operation. This value cannot be changed.

Exit Code

When an error occurs, the error number appears in this box. Use this number to troubleshoot failures by searching for the number in the Microsoft Knowledge Base or provide the number to your technical support staff.

Host Name

Displays the name of the computer or cluster running the Microsoft SQL Server service.

Name

Indicates the display name of the service.

Process ID

Displays the Windows process ID.

SQL Service Type

Displays the type of service provided to calling processes. SQL Server installs several services.

Start Mode

Set this service to the following choices:

  • Manual: This service does not automatically start when the computer starts. You must start the service using SQL Server Configuration Manager, or some other tool.

  • Automatic: This service attempts to start when this computer starts.

  • Disabled: This service cannot be started.

State

Indicates whether this service is running, stopped, or disabled. "…" indicates a state change is pending.


SQL Full-text Filter Daemon Launcher (Advanced)

SQL Full-text Filter Daemon Launcher (Advanced)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Beginning in SQL Server 2008, the SQL Full-text Filter Daemon Launcher service is used by SQL Server full-text. This service must be running if you use full-text search. For information about the filter daemon host processes, see "Full-Text Search Architecture" in SQL Server Books Online.

No properties appear on the Advanced tab by default. If custom properties are defined, they also appear on this tab, with their values.


Unknown Service (Log On Tab)

Unknown Service (Log On Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Services >

Microsoft SQL Server Configuration Manager is unable to identify this service.

SQL Server Configuration Manager receives service information from the WMI provider on the computer running the service. Either there was an error while reading the service properties, or the service properties are not complete. To resolve the problem, try closing and reopening SQL Server Configuration Manager, or check the WMI provider on the computer running the service.

The WMI provider is a Windows component. For information on how to check permissions on the WMI provider, see "How to: Configure WMI to Show Server Status in SQL Server Tools" in SQL Server Books Online.

If you believe you are viewing the correct service, use the Log On tab of the Unknown Service Properties dialog box to specify the account used by this service, and to start and stop the service.

Options

Local System account

Specify a local system account, which does not require a password. However, the local system account may prevent the service from interacting with other servers, depending on the privileges granted to the account.

This account

Specify a local or domain user account that uses Windows Authentication. We recommend using a domain user account with minimal rights for services. For information about selecting an account, "Setting Up Windows Service Accounts" in SQL Server Books Online.

Account Name

Specify the local or domain user account name.

Password

Type the password of the account.

Confirm password

Type the password of the account again.

Start

Start the service.

Stop

Stop the service.

Pause

Pause the service.

Resume

Resume a paused service.


SQL Server 2008 Network Configuration

SQL Server 2008 Network Configuration |
 | See Also
SQL Server Configuration Manager Help >

This section contains the F1 Help topics for the Server Network Configuration dialog boxes in SQL Server Configuration Manager.

See Also

Concepts

SQL Server Configuration Manager Help

© 2010 Microsoft Corporation. All rights reserved.


Protocols for <server> (SQL Server Configuration Manager)

Protocols for <server> (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

To configure the protocols used by the Database Engine, select Protocols for the desired server instance in the console pane, right-click the appropriate protocol in the details pane, and then click Properties.


Protocols for MSSQLSERVER Properties (Flags Tab)

Protocols for MSSQLSERVER Properties (Flags Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

When a certificate is installed on the server, use the Flags tab on the Protocols for MSSQLSERVER Properties dialog box to view or specify the protocol encryption and hide instance options. Microsoft SQL Server must be restarted to enable or disable the ForceEncryption setting.

To encrypt connections, you should provision the SQL Server Database Engine with a certificate. If a certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started. This self-signed certificate can be used instead of a certificate from a trusted certificate authority, but it does not provide authentication or non-repudiation.

Caution:
Secure Sockets Layer (SSL) connections encrypted using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.

For more in formation on encryption, see "Encrypting Connections to SQL Server" in SQL Server Books Online.

The login process is always encrypted. When ForceEncryption is set to Yes, all client/server communication is encrypted, and clients connecting to the Database Engine must be configured to trust the root authority of the server certificate. For more information, see "How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)" in SQL Server Books Online.

Cluster Servers

If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named "test1.<your company>.com" and "test2.<your company>.com" and a virtual server named "virtsql", you need to install a certificate for "virtsql.<your company>.com" on both nodes. You can then check the ForceEncryption check box on the SQL Server Configuration Manager to configure your failover cluster for encryption.

Options

Force Encryption

Force protocol encryption. Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure, even if the transmission packets are viewed during the transmission process. To use channel binding, set Force Encryption to On and configure Extended Protection on the Advanced tab.

Hide Instance

Prevent the SQL Server Browser Service from exposing this instance of the Database Engine to client computers that try to locate the instance by using the Browse button. In the case of named instances on the server, to connect, client applications must specify the protocol endpoint information. For example, the port number or the named pipe name, such as tcp:server,5000. For more information, see Connecting to the SQL Server Database Engine.

For more information, see "How to: Enable Encryption Connections to the Database Engine (SQL Server Configuration Manager)" in Books Online.


Protocols for MSSQLSERVER Properties (Certificate Tab)

Protocols for MSSQLSERVER Properties (Certificate Tab)
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the Certificate tab on the Protocols for MSSQLSERVER Properties dialog box to select a certificate for Microsoft SQL Server, or to view the properties of a certificate. All fields are blank until a certificate is selected.

Certificates are stored locally for the users on the computer. To load a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service.

Page Header

View

Provides access to additional details on the certificate. Not available until a certificate is selected in the Certificate box. For additional information on certificate details, see your Microsoft Windows 2000 or Microsoft Windows Server 2003 documentation.

Clear

Removes the selection from the Certificate box.

Certificate

Name of certificate as determined by security provider. Select a certificate to see the details in the properties grid.

Options

Expiration Date

The final date for the period in which the certificate is valid.

Friendly Name

A friendly or common name for the individual or certification authority to whom the certificate is issued.

Issued By

Information regarding the certification authority that issued the certificate.

Issued To

Information regarding the recipient of the certificate.


Shared Memory Properties

Shared Memory Properties |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the Protocol page on the Shared Memory Properties dialog box to enable or disable the shared memory protocol. Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a Microsoft SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

SQL Server must be restarted to enable or disable the protocol.

Options

Enabled

Possible values are Yes and No. The shared memory protocol is enabled by default.

See Also

Reference

Creating a Valid Connection String Using Shared Memory Protocol

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Named Pipes Properties

Named Pipes Properties |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the Protocol page on the Named Pipes Properties dialog box to view or change the named pipe that Microsoft SQL Server listens to, when using the Named Pipes protocol.

SQL Server must be restarted to enable or disable the protocol, or change the named pipe.

Options

Enabled

Possible values are Yes and No.

Pipe Name

Specifies the named pipe on which SQL Server listens. By default, SQL Server listens on: \\.\pipe\sql\query for the default instance and \\.\pipe\MSSQL$<instancename>\sql\query for a named instance. This field is limited to 2047 characters.

Creating an Alternate Named Pipe

To change the named pipe, type the new pipe name in the Pipe Name box and then stop and restart SQL Server. Since sql\query is well known as the named pipe used by SQL Server, changing the pipe can help reduce the risk of attack by malicious programs.

Example

Type \\.\pipe\unit\app to listen on the unit\app pipe.

Type \\.\pipe\acct to listen on the acct pipe.

See Also

Reference

Creating a Valid Connection String Using Named Pipes

Concepts

Choosing a Network Protocol

Other Resources

How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)

© 2010 Microsoft Corporation. All rights reserved.


TCP/IP Properties (Protocols Tab)

TCP/IP Properties (Protocols Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the TCP/IP Properties dialog box to configure the options for the TCP/IP protocol. Click TCP/IP in the left pane, to show individual IP address configurations in the details pane.

Microsoft SQL Server must be restarted before the changes take effect.

Options

Enabled

Possible values are Yes and No.

Keep Alive

Specify the interval (milliseconds) in which keep-alive packets are transmitted to verify that the computer at the remote end of a connection is still available.

Listen All

Specify whether SQL Server will listen on all the IP addresses that are bound to network cards on the computer. If set to No, configure each IP address separately using the properties dialog box for each IP address. If set to Yes, the settings of the IPAll properties box will apply to all IP addresses. Default value is Yes.

No Delay

SQL Server does not implement changes to this property.

See Also

Reference

Creating a Valid Connection String Using TCP/IP

Concepts

Choosing a Network Protocol

Other Resources

How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)

© 2010 Microsoft Corporation. All rights reserved.


TCP/IP Properties (IP Addresses Tab)

TCP/IP Properties (IP Addresses Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the TCP/IP Properties (IP Addresses Tab) dialog box to configure the TCP/IP protocol options for a specific IP address. Only TCP Dynamic Ports and TCP Port can be configured for all addresses at once by selecting IP All.

Changes take effect when Microsoft SQL Server is restarted. For information about starting and stopping the SQL Server Browser service, see How to: Start and Stop the SQL Server Browser Service in Books Online.

Static vs. Dynamic Ports

The default instance of SQL Server listens for incoming connections on port 1433. The port can be changed for security reasons or because of a client application requirement. By default, named instances (including SQL Server Express) are configured to listen on dynamic ports. To configure a static port, leave the TCP Dynamic Ports box blank and provide an available port number in the TCP Port box. For more information about opening ports in the firewall, see Configuring the Windows Firewall to Allow SQL Server Access in Books Online.

Dynamic Ports

At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance. Using dynamic ports complicates connecting SQL Server through a firewall because the port number may change when SQL Server is restarted, requiring changes to the firewall settings. To avoid connection problems through a firewall, configure SQL Server to use a static port.

Options

Active

Indicates that the IP address is active on the computer. Not available for IPAll.

Enabled

If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to No, this property indicates whether SQL Server is listening on the IP address. If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to Yes, the property is disregarded. Not available for IPAll.

IP Address

View or change the IP address used by this connection. Lists the IP address used by the computer, and the IP loopback address, 127.0.0.1. Not available for IPAll. The IP address can be in either IPv4 or IPv6 format.

TCP Dynamic Ports

Blank, if dynamic ports are not enabled. To use dynamic ports, set to 0.

For IPAll, displays the port number of the dynamic port used.

TCP Port

View or change the port on which SQL Server listens. By default, the default instance of Database Engine listens on port 1433.

SQL Server Database Engine can listen on multiple ports on the same IP address, list the ports, separated by commas, in the format 1433,1500,1501. This field is limited to 2047 characters.

To configure a single IP address to listen on multiple ports, the Listen All parameter must also be set to No, on the Protocols Tab of the TCP/IP Properties dialog box. For more information, see "How to: Configure the Database Engine to Listen on Multiple TCP Ports" in SQL Server Books Online.

Adding or Removing IP Addresses

SQL Server Configuration Manager displays the IP addresses that were available at the time SQL Server was installed. The available IP addresses can change when network cards are added or removed, when a dynamically assigned IP address expires, when the network structure is reconfigured, or when the computer changes its physical location such as a laptop computer connecting to the network in a different building. To change an IP address, edit the IP Address box, and then restart SQL Server.

See Also

Reference

Creating a Valid Connection String Using TCP/IP

Concepts

Choosing a Network Protocol
SQL Server Browser Service

© 2010 Microsoft Corporation. All rights reserved.


VIA Properties

VIA Properties |
 | See Also
SQL Server Configuration Manager Help > SQL Server 2008 Network Configuration >

Use the VIA Properties dialog box to view or specify the following options for the Virtual Interface Adapter (VIA) protocol.

Microsoft SQL Server must be restarted to enable or disable the protocol, or change the settings.

Options

Default Port

Default setting is 0:1433, in the format <network interface card number>:<port number>.

Enabled

Possible values are Yes and No. The VIA protocol is disabled by default.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Listen Info

Default setting is 0:1433, in the format <network interface card number>:<port number>.

When you list multiple ports, separate them with commas. This field is limited to 2047 characters.

Caution:
When more than one instance of SQL Server is installed, examine the port settings and configure each port to be used by only one instance. SQL Server prevents two instances from listening on the same port when using TCP/IP, but not when using VIA. If two instances are listening on the same port, incoming VIA connections may fail to connect, or connect to the unintended instance.

See Also

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


SQL Server Native Client Configuration

SQL Server Native Client Configuration
SQL Server Configuration Manager Help >

This section contains the F1 Help topics for the SQL Server Native Client Configuration dialogs in Microsoft SQL Server Configuration Manager. SQL Server Native Client is the network library that client computers use to connect to SQL Server, starting with Microsoft SQL Server.

The settings configured in SQL Server Native Client Configuration, are used on the computer running the client program. When configured on the computer running SQL Server, they affect only those client programs running on the server.

These settings do not affect clients connecting to previous versions of SQL Server, unless they are using the client tools starting with SQL Server, such as SQL Server Management Studio.

In this Section

  • Creating a Valid Connection String Using VIA Protocol

  • Client Protocols (SQL Server Configuration Manager)

    • Client Protocols Properties (Order Tab)

    • Client Protocols - Shared Memory Properties (Protocol Tab)

    • Client Protocols - TCP/IP Properties (Protocol Tab)

    • Client Protocols - Named Pipes Properties (Protocol Tab)

    • Client Protocols - VIA Properties (Protocol Tab)

  • Aliases (SQL Server Configuration Manager)

    • New Alias (Alias Tab)

    • <Alias> Properties (Alias Tab)

    • Creating a Valid Connection String Using Shared Memory Protocol

    • Creating a Valid Connection String Using TCP/IP

    • Creating a Valid Connection String Using Named Pipes

    • Creating a Valid Connection String Using VIA Protocol


SQL Server Native Client Configuration Properties (Flags Tab)

SQL Server Native Client Configuration Properties (Flags Tab)
SQL Server Configuration Manager Help > SQL Server Native Client Configuration >

Microsoft SQL Server clients on this machine, communicate with SQL Server servers using the protocols provided in the SQL Server Native Client library file. This page provides configures the client computer to request an encrypted connection using Secure Sockets Layer (SSL). If an encrypted connection cannot be established, the connection will fail.

The login process is always encrypted. The options below apply only to encrypting data. For more information about how SQL Server encrypts communication and for instructions on how to configure the client to trust the root authority of the server certificate, see "Encrypting Connections to SQL Server" and "How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)" in SQL Server Books Online.

Options

Force protocol encryption

Request a connection using SSL.

Trust Server Certificate

When set to No, the client process attempts to validate the server certificate. The client and server must have each have a certificate issues from a public certification authority. If the certificate is not present on the client computer, or if the validation of the certificate fails, the connection is terminated.

When set to Yes, the client does not validate the server certificate, thereby enabling the use of a self-signed certificate.

Trust Server Certificate is only available if Force protocol encryption is set to Yes.


Client Protocols (SQL Server Configuration Manager)

Client Protocols (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server Native Client Configuration >

To configure the order that protocols are used by the Microsoft SQL Server clients on this computer, right-click Client Protocols in the console pane, and then click Properties, or right-click the a protocol in the details pane, and then click Order.

To configure the properties of the client protocols used by the SQL Server clients on this computer, in the console pane click Client Protocols, in the details pane right-click the desired protocol, and then click Properties.


Client Protocols Properties (Order Tab)

Client Protocols Properties (Order Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Client Protocols (SQL Server Configuration Manager) >

Use the Order page on the Client Protocols Properties dialog box to view and enable the client protocols.

Click a protocol, and then click Enable or Disable to move the selected protocol to the Disabled Protocols or Enabled Protocols list.

Protocols are tried in the order listed, attempting to connect using the top protocol first, and then the second listed protocol, etc. Move protocols up or down the Enabled Protocols list, by clicking the up arrow and down arrow buttons. When connecting to Microsoft SQL Server from a client on that computer, the Shared Memory protocol will always be tried first, if enabled.

Note:
These settings are not used by Microsoft .NET SqlClient. The protocol order for .NET SqlClient is first TCP, and then named pipes, which cannot be changed.

Options

Disabled Protocols

Lists protocols which are installed but are not currently used.

Enabled Protocols

Lists protocols which are available for Microsoft SQL Server clients on this computer.

>

Enables the currently highlighted protocol in the Disabled Protocols box, moving it to the Enabled Protocols box.

<

Disables the currently highlighted protocol in the Enabled Protocols box, moving it to the Disabled Protocols box.

Up arrow

Moves the highlighted protocol up in the list. This allows you to increase the priority in which the Net-Library will attempt to use the selected protocol for connections.

Down arrow

Moves the highlighted protocol down in the list. This allows you to decrease the priority in which the Net-Library will attempt to use the selected protocol for connections.

Enable Shared Memory Protocol

Enables the shared memory protocol which is always tried first (if enabled), when connecting to SQL Server from a client on that computer.

Note:
If the protocol is specified through a prefix or as part of the connection string, only the specified protocol is attempted.

See Also

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Client Protocols - Shared Memory Properties (Protocol Tab)

Client Protocols - Shared Memory Properties (Protocol Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Client Protocols (SQL Server Configuration Manager) >

In Microsoft SQL Server Configuration Manager use the Protocol tab on the Shared Memory Properties dialog box to view or modify shared memory. For more information about connection strings, see Creating a Valid Connection String Using Shared Memory Protocol.

Options

Enabled

Possible values are Yes and No.

See Also

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Client Protocols - TCP/IP Properties (Protocol Tab)

Client Protocols - TCP/IP Properties (Protocol Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Client Protocols (SQL Server Configuration Manager) >

In Microsoft SQL Server Configuration Manager, use the Protocol tab on the TCP/IP Properties dialog box to view or specify the following options. To connect to a different port, type the port number in the Default Port box. For more information about connection strings, see Creating a Valid Connection String Using TCP/IP.

Options

Default Port

Specifies the port that the TCP/IP Net-library will use to attempt to connect to the target instance of SQL Server. The default value port is 1433.

When connecting to a default instance of Database Engine, the client uses this value. If a default instance has been configured to listen on a different port, change this value to that port number.

When connecting to a named instance of Database Engine, the client will attempt to obtain the port number from the SQL Server Browser Service running on the server computer. If the SQL Server Browser Service is not running, the port number must be provided through this setting, or as part of the connection string.

Enabled

Possible values are Yes and No.

Keep Alive

This parameter (in milliseconds) controls how often TCP attempts to verify that an idle connection is still intact by sending a KEEPALIVE packet. The default is 30000 milliseconds.

Keep Alive Interval

This parameter (in milliseconds) determines the interval separating KEEPALIVE retransmissions until a response is received. The default is 1000 milliseconds.

See Also

Reference

New Alias (Alias Tab)
<Alias> Properties (Alias Tab)

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Client Protocols - Named Pipes Properties (Protocol Tab)

Client Protocols - Named Pipes Properties (Protocol Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Client Protocols (SQL Server Configuration Manager) >

In Microsoft SQL Server Configuration Manager use the Protocol tab on the Named Pipes Properties dialog box to view or modify the description of default pipe. To connect to a different pipe, type the pipe in the Default Pipe box. For more information about connection strings, see Creating a Valid Connection String Using Named Pipes.

Options

Default Pipe

Specifies the default pipe the Named Pipes Net-library will use to attempt to connect to the target instance of SQL Server. By default, SQL Server listens on: \\.\pipe\sql\query

To connect to the default pipe, enter sql\query

Enabled

Possible values are Yes and No.

See Also

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Client Protocols - VIA Properties (Protocol Tab)

Client Protocols - VIA Properties (Protocol Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Client Protocols (SQL Server Configuration Manager) >

In Microsoft SQL Server Configuration Manager, use the Protocol tab on the VIA Properties dialog box to view or specify the following options for the Virtual Interface Adapter. For more information about connection strings, see Creating a Valid Connection String Using VIA Protocol.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Options

Default NIC

Indicates which Network Interface Card (NIC) the VIA protocol is bound to. NICs are numbered, starting at zero. Computers with only one NIC will indicate 0.

Default Server

VIA port that VIA is listening on when accepting connections from VIA clients.

Enabled

Possible values are Yes and No.

See Also

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Aliases (SQL Server Configuration Manager)

Aliases (SQL Server Configuration Manager)
SQL Server Configuration Manager Help > SQL Server Native Client Configuration >

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. To create an alias for the Microsoft SQL Server clients on this computer, right-click Aliases in the console pane, and then click New Alias. To configure an existing alias for the SQL Server clients on this computer, click Aliases in the console pane, right-click the desired existing alias in the details pane, and then click Properties.


New Alias (Alias Tab)

New Alias (Alias Tab)
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Use the Alias page on the Alias - New dialog box to specify the elements of the connection string for an alias. To change the connection string of an existing alias, see <Alias> Properties (Alias Tab).

All values in the Properties grid do not have to be completed. Valid combinations vary depending on the protocol selected. See the topics listed below for examples of valid combinations.

Alias Name

The name (alias) that you want to use to refer to this connection.

Pipe Name / Port No / VIA Parameters

Additional elements of the connection string. The name of this box varies with the selected protocol.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Protocol

The protocol used for the connection.

Server

The name of the Microsoft SQL Server instance being connected to.

When to Use an Alias

By default, SQL Server connects to a local instance of SQL Server using the Shared Memory protocol, and to an instance of SQL Server on another computer using either TCP/IP or Named Pipes. Create an alias when you are using TCP/IP, named pipes, or VIA, and you want to provide a customized connection string, or when you want to use a name other than the server name for the connection.

Examples

  • SQL Server is not listening on the default TCP/IP port of 1433 so you want to provide a connection string with a different port number.

  • SQL Server is not listening on the default named pipe so you want to provide a connection string with a different pipe name.

  • An application expects to connect to a database on the server named ACCT, but that database has been consolidated as an instance named ACCT on a server named CENTRAL. The application cannot easily be changed. Create an alias named ACCT, with a connection string pointing to CENTRAL\ACCT.

Creating a Valid Connection String

See the following topics for a description and examples of valid combinations of alias properties:

  • Creating a Valid Connection String Using Shared Memory Protocol

  • Creating a Valid Connection String Using TCP/IP

  • Creating a Valid Connection String Using Named Pipes

  • Creating a Valid Connection String Using VIA Protocol


<Alias> Properties (Alias Tab)

<Alias> Properties (Alias Tab) |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Use the Alias page on the <Alias name> Properties dialog box to view or specify the elements of connection string of an Alias.

Options

Alias Name

The name (alias) that you want to use to refer to this connection.

Pipe Name / Port No / VIA Parameters

Additional elements of the connection string. The name of this box varies with the selected protocol. See the topics listed below for examples.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Protocol

The protocol used for the connection.

Server

The name of the Microsoft SQL Server instance being connected to.

See Also

Reference

Creating a Valid Connection String Using Shared Memory Protocol
Creating a Valid Connection String Using TCP/IP
Creating a Valid Connection String Using Named Pipes
Creating a Valid Connection String Using VIA Protocol

© 2010 Microsoft Corporation. All rights reserved.


Creating a Valid Connection String Using Shared Memory Protocol

Creating a Valid Connection String Using Shared Memory Protocol |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

Connections to Microsoft SQL Server from a client running on the same computer use the shared memory protocol. Shared memory has no configurable properties. Shared memory is always tried first, and cannot be moved from the top position of the Enabled Protocols list in the Client Protocols Properties list. The Shared Memory protocol can be disabled, which is useful when troubleshooting one of the other protocols.

You cannot create an alias using the shared memory protocol, but if shared memory is enabled, then connecting to the Database Engine by name, creates a shared memory connection. A shared memory connection string uses the format lpc:<servername>[\instancename].

Connecting to the Local Server

When connecting to SQL Server running on the same computer as the client, you can use (local) as the server name. This is not encouraged as it leads to ambiguity, however it can be useful when the client is known to be running on the intended computer. For instance, when creating an application for mobile disconnected users, such as a sales force, where SQL Server will run on laptop computers and store project data, a client connecting to (local) would always connect to the SQL Server running on the laptop. The word localhost or a period (.) can be used in place of (local).

Verifying your Connection Protocol

The following query will return the protocol used for the current connection.

Copy Code
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Examples:

The following names will connect to the local computer with the shared memory protocol if it is enabled:

<servername>

<servername>\<instancename>

(local)

localhost

You cannot create an alias for a shared memory connection.

Note:
Specifying an IP Address in the Server box will result in a TCP/IP connection.

See Also

Reference

Creating a Valid Connection String Using TCP/IP
Creating a Valid Connection String Using Named Pipes
Creating a Valid Connection String Using VIA Protocol

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Creating a Valid Connection String Using TCP/IP

Creating a Valid Connection String Using TCP/IP |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

To create a valid connection string using TCP/IP, you must:

  • Specify an Alias Name.

  • For the Server, enter either a server name to which you can connect using the PING utility, or an IP address to which you can connect using the PING utility. For a named instance append the instance name.

  • Specify TCP/IP for the Protocol.

  • Optionally, enter a port number for the Port No. The default is 1433, which is the port number of the default instance of the Database Engine on a server. To connect to a named instance or a default instance that is not listening on port 1433, you must provide the port number, or start the SQL Server Browser service. For information on configuring the SQL Server Browser service, see SQL Server Browser Service.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and port values from the registry for the specified alias name, and creates a connection string in the format tcp:<servername>[\<instancename>],<port> or tcp:<IPAddress>[\<instancename>],<port>.

Note:
Microsoft Windows XP Service Pack 2 enables Windows Firewall, which closes port 1433 by default. Because Microsoft SQL Server communicates over port 1433, you must reopen the port if SQL Server is configured to listen for incoming client connections using TCP/IP. For information on configuring a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online or review your firewall documentation.

SQL Server and SQL Server Native Client fully support both Internet Protocol version 4 (IPv4) and Internet Protocol version 6 (IPv6). SQL Server Configuration Manager accepts both IPv4 and IPv6 formats for IP addresses. For information on IPv6, see "Connecting Using IPv6" in SQL Server Books Online.

Connecting to the Local Server

When connecting to SQL Server running on the same computer as the client, you can use (local) as the server name. This is not encouraged as it leads to ambiguity, however it can be useful when the client is known to be running on the intended computer. For instance, when creating an application for mobile disconnected users, such as a sales force, where SQL Server will run on laptop computers and store project data, a client connecting to (local) would always connect to the SQL Server running on the laptop. The word localhost or a period (.) can be used in place of (local).

Verifying Your Connection Protocol

The following query returns the protocol used for the current connection.

Copy Code
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Examples

Connecting by server name:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             <servername>

Connecting by server name to a named instance:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             <servername>\<instancename>

Connecting by server name to a specified port:

Copy Code
Alias Name         <serveralias>
Port No            <port>
Protocol           TCP/IP
Server             <servername>

Connecting by IP address:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             <IPAddress>

Connecting by IP address to a named instance:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             <IPAddress>\<instancename>

Connecting by IP address to a specified port:

Copy Code
Alias Name         <serveralias>
Port No            <port number>
Protocol           TCP/IP
Server             <IPAddress>

Connecting to the local computer using (local):

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             (local)

Connecting to the local computer using localhost:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             localhost

Connecting to a named instance on the local computer localhost:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             localhost\<instancename>

Connecting to the local computer using a period:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             .

Connecting to a named instance on the local computer using a period:

Copy Code
Alias Name         <serveralias>
Port No            <blank>
Protocol           TCP/IP
Server             .\<instancename>
Note:
For information on specifying the network protocol as a sqlcmd parameter, see "How to: Connect to the Database Engine Using sqlcmd.exe" in SQL Server Books Online.

See Also

Reference

Creating a Valid Connection String Using Shared Memory Protocol
Creating a Valid Connection String Using Named Pipes
Creating a Valid Connection String Using VIA Protocol

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Creating a Valid Connection String Using Named Pipes

Creating a Valid Connection String Using Named Pipes |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

Unless changed by the user, when the default instance of Microsoft SQL Server listens on the named pipes protocol, it uses \\.\pipe\sql\query as the pipe name. The period indicates that the computer is the local computer, pipe indicates that the connection is a named pipe, and sql\query is the name of the pipe. To connect to the default pipe, the alias must have \\<computer_name>\pipe\sql\query as the pipe name. If SQL Server has been configured to listen on a different pipe, the pipe name must use that pipe. For instance, if SQL Server is using \\.\pipe\unit\app as the pipe, the alias must use \\<computer_name>\pipe\unit\app as the pipe name.

To create a valid pipe name, you must:

  • Specify an Alias Name.

  • Select Named Pipes as the Protocol.

  • Enter the Pipe Name. Alternatively, you can leave Pipe Name blank and SQL Server Configuration Manager will complete the appropriate pipe name after you specify the Protocol and Server

  • Specify a Server. For a named instance you can provide a server name and instance name.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and pipe name values from the registry for the specified alias name, and creates a pipe name in the format np:\\<computer_name>\pipe\<pipename> or np:\\<IPAddress>\pipe\<pipename>. For a named instance, the default pipe name is \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query.

Note:
Microsoft Windows XP Service Pack 2 enables Windows Firewall, which closes port 445 by default. Because Microsoft SQL Server communicates over port 445, you must reopen the port if SQL Server is configured to listen for incoming client connections using named pipes. For information on configuring a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online or review your firewall documentation.

Connecting to the Local Server

When connecting to SQL Server running on the same computer as the client, you can use (local)as the server name. Using (local) is not encouraged because it leads to ambiguity; however it can be useful when the client is known to be running on the intended computer. For instance, when creating an application for mobile disconnected users, such as a sales force, where SQL Server will run on laptop computers and store project data, a client connecting to (local) would always connect to the SQL Server running on the laptop. The word localhost or a period (.) can be used in place of (local).

Verifying Your Connection Protocol

The following query will return the protocol used for the current connection.

Copy Code
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Examples

Connecting by server name to the default pipe:

Copy Code
Alias Name         <serveralias>
Pipe Name          <blank>
Protocol           Named Pipes
Server             <servername>

Connecting by IP Address to the default pipe:

Copy Code
Alias Name         <serveralias>
Pipe Name          <leave blank>
Protocol           Named Pipes
Server             <IPAddress>

Connecting by server name to a non-default pipe:

Copy Code
Alias Name         <serveralias>
Pipe Name          \\<servername>\pipe\unit\app
Protocol           Named Pipes
Server             <servername>

Connecting by server name to a named instance:

Copy Code
Alias Name         <serveralias>
Pipe Name          \\<servername>\pipe\MSSQL$<instancename>\SQL\query
Protocol           Named Pipes
Server             <servername>

Connecting to the local computer using localhost:

Copy Code
Alias Name         <serveralias>
Pipe Name          <blank>
Protocol           Named Pipes
Server             localhost

Connecting to the local computer using a period:

Copy Code
Alias Name         <serveralias>
Pipe Name          <left blank>
Protocol           Named Pipes
Server             .
Note:
To specify the network protocol as a sqlcmd parameter, see "How to: Connect to the Database Engine Using sqlcmd.exe" in SQL Server Books Online.

See Also

Reference

Creating a Valid Connection String Using Shared Memory Protocol
Creating a Valid Connection String Using TCP/IP
Creating a Valid Connection String Using VIA Protocol

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


Creating a Valid Connection String Using VIA Protocol

Creating a Valid Connection String Using VIA Protocol |
 | See Also
SQL Server Configuration Manager Help > SQL Server Native Client Configuration > Aliases (SQL Server Configuration Manager) >

To create a valid connection string using Virtual Interface Adapter (VIA), you must provide each of the following elements:

  • Specify an Alias Name.

  • Provide the network interface card (NIC) number followed by a port number in the Via Parameters box. If left blank, VIA will attempt to connect to port <NIC#>:1433.

  • Specify VIA as the Protocol.

  • In the Server field, enter the server name or <servername>\<instancename> for a named instance.

At the time of connection, the SQL Server Native Client component reads the Server, Protocol, and Via Parameters values from the registry for the specified Alias Name, and creates a connection string in the format via:<servername> [\instancename],<nic number>:<port>.

Important:
When two or more instances of Microsoft SQL Server are installed on the same computer, VIA connections can be ambiguous. When enabled, the VIA protocol attempts to use the TCP/IP settings, and listens on port 0:1433. For VIA drivers that do not allow configuration of the port, both instances of SQL Server will be listening on the same port. Incoming client connections can receive a connection to the correct server instance, the incorrect server instance, or be denied a connection, because the port is in use.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Verifying your Connection Protocol

The following query will return the protocol used for the current connection.

Copy Code
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Examples:

Connecting by server name:

Copy Code
Alias Name         ServerAlias
Via Parameters     0:1433
Protocol           VIA
Server             <servername>

Connecting to a named instance by server name:

Copy Code
Alias Name         ServerAlias
Via Parameters     0:1433
Protocol           VIA
Server             <servername>\<instancename>
Note:
To specify the network protocol as a sqlcmd parameter, search Books Online for the topic "How to: Connect to the Database Engine Using sqlcmd.exe."

See Also

Reference

Creating a Valid Connection String Using Shared Memory Protocol
Creating a Valid Connection String Using TCP/IP
Creating a Valid Connection String Using Named Pipes

Concepts

Choosing a Network Protocol

© 2010 Microsoft Corporation. All rights reserved.


SQL Server Browser Service

SQL Server Browser Service |
 | See Also
SQL Server Configuration Manager Help >

The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

  • Browsing a list of available servers

  • Connecting to the correct server instance

  • Connecting to dedicated administrator connection (DAC) endpoints

For each instance of the Database Engine and SSAS, the SQL Server Browser service (sqlbrowser) provides the instance name and the version number. SQL Server Browser is installed with SQL Server, and provides this service for earlier versions of SQL Server that are running on that computer, starting with SQL Server 7.0.

SQL Server Browser can be configured during setup or by using SQL Server Configuration Manager. By default, the SQL Server Browser service starts automatically:

  • When upgrading an installation.

  • When installing side-by-side with an instance of SQL Server 2000.

  • When installing on a cluster.

  • When installing a named instance of the SQL Server Database Engine including all instances of SQL Server Express.

  • When installing a named instance of Analysis Services.

Background

Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.

How SQL Server Browser Works

When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

For information about starting and stopping the SQL Server Browser service, see How to: Start and Stop the SQL Server Browser Service.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Using SQL Server Browser

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

  • Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

  • Any component that generates or passes server\instance information that could later be used by other components to reconnect.

  • Connecting to a named instance without providing the port number or pipe.

  • DAC to a named instance or the default instance if not using TCP/IP port 1433.

  • The OLAP redirector service.

  • Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

  • You must update and maintain client application code to ensure it is connecting to the proper port.

  • The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.

Side-by-Side Installation with SQL Server 2000

In SQL Server 2000, the identification of the server connection endpoints is performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, you must make sure that SQL Server 2000 or MSDE is upgraded to Service Pack 3a (SP3a) or later. Versions earlier than SP3a do not properly share port 1434 and may not make your instances of SQL Server available to requesting client applications. Although you can change the services startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, we recommend that you update all earlier versions of SQL Server to the latest service pack.

When an instance of SQL Server 2000 that is not updated to at least service pack 3a is installed on the computer, if the SQL Server Browser is not running, the SQL Server 2000 listener service starts. If SQL Server Browser starts after the listener service, it waits 5 seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser fails to start. To resolve this problem with versions of SQL Server 2000 earlier than Service Pack 3a, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service continues to attempt to start on port 1434; therefore, the instance of SQL Server 2000 should be upgraded to Service Pack 3a as soon as possible.

SQL Server 7.0 has no similar capabilities and has no conflicts with SQL Server Browser.

Clustering

SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.

Note:
When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters.

Installing, Uninstalling, and Running from the Command Line

By default, the SQL Server Browser program is installed at C:\Program Files\Microsoft SQL Server\100\Shared\sqlbrowser.exe. If SQL Server 2000 is installed on the computer but not upgraded to Service Pack 3, and if the SQL Server 2000 instance is not being upgraded, SQL Server 2000 should be stopped during SQL Server installation. If SQL Server 2000 is running, SQL Server Browser cannot start and claim port 1434, and the installation may fail due to lack of connectivity.

The SQL Server Browser service is uninstalled when the last instance of SQL Server is removed. Instances of SQL Server 2000 resume their original behavior.

SQL Server Browser can be started from the command prompt for troubleshooting, by using the -c switch:

Copy Code
<drive>\<path>\sqlbrowser.exe -c

Security

Account Privileges

SQL Server Browser listens on a UDP port and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP). SQL Server Browser should be run in the security context of a low privileged user to minimize exposure to a malicious attack. The logon account can be changed by using the SQL Server Configuration Manager. The minimum user rights for SQL Server Browser are the following:

  • Deny access to this computer from the network

  • Deny logon locally

  • Deny Log on as a batch job

  • Deny Log On Through Terminal Services

  • Log on as a service

  • Read and write the SQL Server registry keys related to network communication (ports and pipes)

Default Account

Setup configures SQL Server Browser to use the account selected for services during setup. Other possible accounts include the following:

  • Any domain\local account

  • The local service account (not available on W2K platforms)

  • The local system account (not recommended as has unnecessary privileges)

Hiding SQL Server

Hidden instances are instances of SQL Server that support only shared memory connections. For SQL Server, set the HideInstance flag to indicate that SQL Server Browser should not respond with information about this server instance. SQL Server Browser also supports the SQL Server 2000 method of hiding instances enabled by setting the HideServer option through the server network utility. For information about configuring the HideServer option, see "How to: Hide an Instance of SQL Server Database Engine" in SQL Server Books Online.

Using a Firewall

To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434, in addition to the TCP port used by SQL Server (e.g., 1433). For information about working with a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online.

See Also

Other Resources

Network Protocols and Network Libraries

© 2010 Microsoft Corporation. All rights reserved.


Connecting Using IPv6

Connecting Using IPv6 |
 | See Also
SQL Server Configuration Manager Help >

SQL Server and SQL Server Native Client fully support both Internet Protocol version 4 (IPv4) and Internet Protocol version 6 (IPv6). When Windows is configured with IPv6 SQL Server, components automatically recognize the existence of IPv6. No special SQL Server configuration is necessary.

Support includes but is not limited to the following:

  • The SQL Server Database Engine and the other server components can listen on both IPv4 and IPv6 addresses at the same time. When both IPv4 and IPv6 are present, you can use SQL Server Configuration Manager to configure the Database Engine to listen only on IPv4 addresses or only on IPv6 addresses.

  • When the SQL Server Browser service running on a machine that supports both IPv4 and IPv6 is queried on an IPv4 address, it responds with an IPv4 address and the first IPv4 TCP port in its list. When queried on an IPv6 address, it responds with an IPv6 address and the first IPv6 TCP port in its list. To avoid inconsistency, we recommend that the IPv4 and IPv6 listeners be configured to listen to the same port.

  • Tools such as SQL Server Management Studio and SQL Server Configuration Manager accept both IPv4 and IPv6 formats for IP addresses. In most cases, the connection string does not need to be modified if the <computer_name>\<instance_name> is specified using server hostname or fully qualified domain name (FQDN). If the server computer has both IPv4 and IPv6, its hostname or FQDN will be resolved into multiple IP addresses, including at least one IPv4 address and multiple IPv6 addresses. SQL Server Native Client attempts to establish connections using these IP addresses in the order received from TCP/IP and uses the first connection that succeeds. Because the order cannot be predicted by SQL Server Native Client, this should be regarded as random order. IPv4 addresses are attempted first if both IPv4 and IPv6 addresses are present. This logic is transparent to the users of ODBC, OLE DB, or ADO.NET.

    Note:
    If the Database Engine is not listening on IPv4, the attempted IPv4 connection must wait for the time-out period before the IPv6 address is attempted. To avoid this, connect directly to the IPv6 IP address or configure an alias on the client with the IPv6 address.

See Also

Other Resources

SQL Server Configuration Manager

© 2010 Microsoft Corporation. All rights reserved.


Choosing a Network Protocol

Choosing a Network Protocol
SQL Server Configuration Manager Help >

To connect to SQL Server Database Engine you must have a network protocol enabled. Microsoft SQL Server can service requests on several protocols at the same time. Clients connect to SQL Server with a single protocol. If the client program does not know which protocol SQL Server is listening on, configure the client to sequentially try multiple protocols. Use SQL Server Configuration Manager to enable, disable, and configure network protocols.

Shared Memory

Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

Note:
Clients that use MDAC 2.8 or earlier cannot use shared memory protocol. If these clients try this, they are automatically switched to the named pipes protocol.

TCP/IP

TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today. Configuring your computer to use TCP/IP can be complex, but most networked computers are already correctly configured. To configure the TCP/IP settings that are not exposed in SQL Server Configuration Manager, see the Microsoft Windows documentation.

Named Pipes

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

VIA

Virtual Interface Adapter (VIA) protocol works with VIA hardware. For information about how to use VIA, contact your hardware vendor.

Important:
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Named Pipes vs. TCP/IP Sockets

In a fast local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable with regard to performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the interprocess communication (IPC) mechanisms communicate between peers.

For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.

It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer that is running an instance of SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is very fast.

For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network. Depending on the type of applications, such performance differences can be significant.

TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.

Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

Enabling the Protocol

The protocol must be enabled on both the client and server to work. The server can listen for requests on all enabled protocols at the same time. Client computers can pick one, or try the protocols in the order listed in SQL Server Configuration Manager.

Note:
Microsoft SQL Server does not support the Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols. Clients previously connecting with these protocols must select a different protocol to connect to SQL Server.

For a short tutorial about how to configure protocols and connect to the Database Engine, see Tutorial: Getting Started with the Database Engine.


Managing SQL Server 2000 Services

Managing SQL Server 2000 Services
SQL Server Configuration Manager Help >

SQL Server 2000 services cannot be managed with SQL Server Management Studio or SQL Server Configuration Manager. Use SQL Server 2000 Enterprise Manager to change service accounts, Service Manager to start and stop services, Server Network Utility to manage server protocols, and Client Network Utility to manage client protocols and aliases.


Monitoring the Error Logs

Monitoring the Error Logs
SQL Server Configuration Manager Help >

SQL Server logs certain system events and user-defined events to the SQL Server error log and the Microsoft Windows application log. Both logs automatically timestamp all recorded events. Use the information in the SQL Server error log to troubleshoot problems related to SQL Server.

The Windows application log provides an overall picture of events that occur on the Windows operating system, as well as events in SQL Server and SQL Server Agent. Use the Windows Event Viewer to view the Windows application log and to filter the information. For example, you can filter events, such as information, warning, error, success audit, and failure audit.

Comparing Error and Application Log Output

You can use both the SQL Server error log and the Windows application log to identify the cause of problems. For example, while monitoring the SQL Server error log, you may encounter error messages that do not contain cause information. By comparing the dates and times for events between these logs, you can narrow the list of probable causes. The SQL Server Management Studio Log File Viewer lets you integrate SQL Server, SQL Server Agent, and the Windows logs into a single list, making it easy to understand related server events and SQL Server events. For more information, see the topic "Log File Viewer" in SQL Server Books Online.

In This Section

Topic | Description
Viewing the SQL Server Error Log | Contains information about the SQL Server error log and how to view it.
Viewing the Windows Application Log | Contains information about the Windows application log and how to view it.


Viewing the SQL Server Error Log

Viewing the SQL Server Error Log
SQL Server Configuration Manager Help > Monitoring the Error Logs >

View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.

View the SQL Server error log by using SQL Server Management Studio or any text editor. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

A new error log is created each time an instance of SQL Server is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension.


Viewing the Windows Application Log

Viewing the Windows Application Log
SQL Server Configuration Manager Help > Monitoring the Error Logs >

When SQL Server is configured to use the Microsoft Windows application log, each SQL Server session writes new events to that log. Unlike the SQL Server error log, a new application log is not created each time you start an instance of SQL Server.

View and manage the Windows application log by using Windows Event Viewer or the Log Viewer in SQL Server Management Studio.

There are three logs that can be viewed with Event Viewer.

Windows log type | Description
System log | Records events logged by the Windows operating system components. For example, the failure of a driver or other system component to load during startup is recorded in the system log.
Security log | Records security events, such as failed login attempts. This helps track changes to the security system and identify possible breaches to security. For example, attempts to log on to the system may be recorded in the security log, depending on the audit settings in the User Manager. Only members of the sysadmin fixed server role can view the security log.
Application log | Records events that are logged by applications. For example, a database application might record a file error in the application log.

For more information about using Event Viewer, managing the application log, and understanding the information it presents, see the Windows documentation.
To view the Windows application log

How to: View the Windows Application Log (Windows)


Legal Notice for Documentation

Legal Notice for Documentation
SQL Server Configuration Manager Help >

This document supports a the final commercial release of the software program "SQL Server 2008 R2". This document is the confidential and proprietary information of Microsoft Corporation.

This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. Some examples depicted in this document are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.

This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

© 2010 Microsoft Corporation. All rights reserved.

Microsoft, Active Directory, ActiveX, Bing Maps, Excel, IntelliSense, MSDN, MS-DOS, PivotChart, PivotTable, PowerPoint, SharePoint, SQL Server, Visual Basic, Visual C#, Visual C++, Visual Studio, Windows, Windows NT, Windows Server, and Windows Vista are trademarks of the Microsoft group of companies.

SAP NetWeaver is the registered trademark of SAP AG in Germany and in several other countries.

All other trademarks are property of their respective owners.

Documentation Policy for SQL Server Support and Upgrade

Documentation - Support and Upgrade Policy

Microsoft SQL Server 2008 R2 software documentation includes SQL Server Books Online (“BOL”), readme files, known issues documents, sample code/applications, sample databases, and Knowledge Base articles published by Microsoft, which all contain content about SQL Server features and functionality (all the “Documentation”). The summary below describes the support and upgrade policy for certain features and functionality referenced in the Documentation.

Samples. The primary purpose of a sample contained within Documentation is to illustrate a concept, or a reasonable use of a particular statement or clause. Most samples do not include all of the code that may normally be found in a full production system, as some of the usual data validation and error handling is removed to focus the sample on a particular concept or statement. Microsoft Customer Support Services is not available for any samples contained in the Documentation.

Placeholders. In some cases, a particular feature in SQL Server 2008 R2 has not been sufficiently tested or developed for use. Such features are often listed in a BOL, readme file, or other Documentation as placeholders and are only intended as notations for Microsoft internal reference. Such features are not intended for your use and are not supported by Microsoft Customer Support Services. The attempted use of such features may result in data loss.

Third Party Content. Unless the Documentation states otherwise, third party content, including issues that result from using such third party content with SQL Server 2008 R2, are not supported by Microsoft Customer Support Services.

Undocumented Entry Points. Microsoft Customer Support Services does not provide support for databases or applications that leverage or use undocumented entry points like undocumented APIs, including but not limited to: stored procedures, extended stored procedures, functions, views, tables, columns, properties, or metadata. Such use may result in data loss.

Upgrades. Server and database upgrades to future versions of SQL Server 2008 R2 for applications and databases that leverage and use undocumented entry points also may not work and are not supported by Microsoft Customer Support Services.

For more information about the Microsoft Support Lifecycle policy, visit the following Web site: http://support.microsoft.com/lifecycle.


About the SQL Server License Terms

About the SQL Server License Terms |
 | See Also
SQL Server Configuration Manager Help >

The Microsoft Software License Terms are copied to the local computer when SQL Server is installed. When multiple instances of the same edition and language of SQL Server are installed on the same computer, a single copy of the license terms will apply to all instances of that edition and language. The files are written to: %ProgramFiles%\Microsoft SQL Server\100\License Terms\ or %ProgramFiles(x86)%\Microsoft SQL Server\100\License Terms\.

When multiple instances of the same SQL Server edition and language are installed on the same computer, a single copy of the license terms will apply to all instances of that edition and language.

The license terms file name indicates the edition of SQL Server as well as the SQL Server language edition. For example:

  • License_DEV_ENU.txt for SQL Server Developer, English language edition.

  • License_STD_ITA.txt for SQL Server Standard, Italian language edition.

  • License_UA_JPN.txt for SQL Server Upgrade Advisor, Japanese language edition.

  • License_SQLNCLI_ENU.txt for SQL Server Native Client, English language edition.

  • License_SQLXML4_KOR.txt for SQLXML, Korean language edition.

The following file name segments are used to identify SQL Server editions:

  • DC - Datacenter

  • ENT - Enterprise

  • DEV - Developer

  • STD - Standard

  • WKGP - Workgroup

  • WEB - Web

  • EXPR - Express

  • UA - Upgrade Advisor

The following file name segments are used to identify SQL Server languages:

  • ENU - English

  • JPN - Japanese

  • FRA - French

  • ITA - Italian

  • KOR - Korean

  • DEU - German

  • ESN - Spanish

  • CHS - Simplified Chinese

  • CHT - Traditional Chinese

  • NLA - Dutch

  • PTB - Portuguese (Brazilian)

  • SVE - Swedish

  • RUS - Russian

SQL Server Licensing Options

For more information about SQL Server pricing and licensing options, see the Microsoft SQL Server 2008 R2 Web site.

See Also

Concepts

Legal Notice for Documentation

Other Resources

SQL Server 2008 R2 Privacy Statement

© 2010 Microsoft Corporation. All rights reserved.