Following is an example of the client-side error message that results if the TSQL Default TCP endpoint is stopped and you try to connect to it:
A connection was successfully established with the server, but then an error occurred during the login process.
Now that you know a bit about endpoints, let’s go a bit deeper and explore how client connections are facilitated on the server.
The Role of SQL Browser
You might be surprised to learn that when clients try to connect to SQL Server 2008, their first network access is made over UDP Port 1434 to the SQL Browser service.
Regardless of the encryption status of the connection itself, login credentials are always encrypted when passed to SQL Server 2008 (to foil any malicious packet sniffing). If a certificate signed by an external authority (such as VeriSign) is not installed on the server, SQL Server automatically generates a self-signed certificate for use in encrypting login credentials.
SQL Browser is the upgrade to the SQL Server Resolution Protocol (SSRP) and its job is to hand out instance names, version numbers, and connection information for each (nonhidden) instance of the Database Engine (and Analysis Services) residing on a server—not only for SQL Server 2008 instances, but for SQL Server 2000 and 2005 instances as well. When clients connect by name, SQL Browser searches for that name in its list and then hands out the connection data for that instance. It can also provide a list of available servers and help make connections to the correct server instance or to make a connection on the dedicated administrator connection (DAC).
Ports, Pipes, and Instances
Default instances of SQL Server 2008 are automatically configured (just as in previous editions) to listen on all IP addresses and TCP Port 1433.
Named instances, on the other hand, are automatically configured to listen on all IP addresses, using dynamic TCP port numbers that change when the Database Engine is restarted. (Most often, these change only when the port number last used by the service is in use by a different application.) If the SQL Browser service is not running, the client might need to provide additional connection information to be able to connect to SQL Server. The additional connection information includes the specific port or pipe that the SQL Server instance may be listening on. The only exception to this is if the server is listening on the default port of 1433. Otherwise, the client must specify the port when connecting with TCP/IP.
When dynamic ports are used, the port number can change at any given time and cause your clients to have to change their port to match the new server port. SQL Browser, therefore, is configured to autostart on servers that contain one or more named instances so that clients can connect by simply providing the server name. The complexity associated with providing additional port or pipe information is avoided when the SQL Browser service is running. SQL Browser is also required for enumerating the server lists used to connect with client tools such as SMSS.
If named instances have fixed port numbers known to clients, or if a pipe name is well known, SQL Browser is not required to connect.
For named pipes, the default instance’s pipe name is \sql\query; for named instances, the pipe name is MSSQL$instancename\sql\query.
When a link is made, endpoint provisioning kicks in to finalize (or reject) the connection.
Now that you have acquired some knowledge about the most important server-side networking considerations, it’s time to learn how to install and configure the client-side half of the equation.
All SQL Server 2008 installations (including client-tools-only or SNAC-only installations) require Windows Installer 4.5, which is freely downloadable from Microsoft. It can also be installed as part of the SQL Server Installation Wizard or manually installed from the SQL Server media. The location of the installer media varies depending on the media you are using but an example of the location is as follows:
The same operating system requirements for server installations apply to client tools and SNAC installations, with one exception: When you install SNAC by itself on top ofWindows XP, only SP1 is required, and when you install SNAC on top of Windows Server 2003, SP1 is not required. You can review the complete list of requirements in Chapter, “Installing SQL Server 2008,” in the section “Installation Requirements.” Note that SNAC and the client tools both depend on the presence of the .NET Framework 3.5 SP1, and the client tools in turn depend on SNAC. Setup automatically installs both Framework 3.5 SP1 and SNAC, when required, on the target machine. If incompatible or beta versions exist that must be uninstalled first, Setup lets you know to use Installer 4.5.
Installing the Client Tools
To install the SQL Server 2008 client tools, you start Setup normally and follow the prompts. When the Feature Selection screen appears, you check only the Client Tools Connectivity check box, as shown in below pic.
Online sql dba learning
For detailed information on how to write C++ code by using the header and library files included in the SNAC software development kit (SDK), see the Books Online topic “Using the SQL Native Client Header and Library Files.” Type your paragraph here.
The SNAC installer has two primary options (as shown in the below pic):
By default, all network protocols except for VIA are enabled on the client during installation. Basically that's how you install SNAC
From this screen, you can right-click any of any protocols to change their enabled state, view Properties pages, or change the default connection order (except that of shared memory, which is always tried first and whose order cannot be changed). The following is the default connection order for clients connecting without the benefit of a server alias, connection string, or other means:
(As the grid shows, VIA is disabled by default.) When you are connecting remotely, TCP/IP is the first protocol attempted because shared memory is local only.
When a client does not specify a connection protocol, SNAC (SQL Native Client) automatically tries each protocol in the list in sequence, according to the Order column. The first protocol to connect successfully wins. If the winning connection is subsequently rejected by the server for any reason, no other protocols are tried. Note also that local clients using MDAC 2.8 or lower cannot connect using shared memory, and they are automatically switched to named pipes if they attempt to do so.
Let’s examine one of the protocols. To start, you need to double-click TCP/IP under the Name column to open the TCP/IP Properties screen (as shown in the next picture).
The same kind of install can be done quietly from the command line (Setup doubles as a command-line application), using the following:
driveletter:\Servers\Setup> Setup.exe /q /ACTION=Install /FEATURES=CONN /INSTANCENAME=INST2008
That’s all there is to it! You will be happy to learn that the SQL Server 2008 client tools can safely be installed side by side with your SQL Server 2000 or 2005 client tools. You can even access databases and other objects created in either edition (with a few notable exceptions, such as database diagrams) by using either toolset. The sections that follow describe how to install and use a few of the client tools for client configuration and testing.
Installing SNAC (SQL Native Client)
This section shows how easy it is to install SNAC, the key net-library for SQL Server 2008 and beyond. As mentioned earlier, both the SQL Server 2008 Database Engine and the client tools depend on SNAC. SNAC is installed when you install the SQL Server connectivity tools, or you can simply launch it on its own from the SQL Server installation medium by running driveletter:\Servers\Setup\sqlncli.msi. The following
Redistributing SNAC with Custom Client Applications
If you build an application that relies on SNAC, you need to be aware that it can be redistributed in two ways:
When you are building MSI files for an application, it is important that you register sqlncli.msi as a package dependency (and, of course, to install it as well, if it is not present on the destination machine). This helps ensure that SNAC will not be accidentally uninstalled from the destination machine without first flashing a warning to
The program name for SNAC found in the Add or Remove Programs Control Panel applet is Microsoft SQL Server 2008 Native Client, not SQL Native Client, as it is commonly known.
Client configuration is a many-leveled beast, consisting of operating system tasks such as installing protocols, application tasks such as choosing or coding to a specific Application Programming Interface (API), provider, or driver, and maintenance tasks such as configuring network settings, building connection strings, and so on. The following sections cover a broad range of these tasks, focusing on the most common. Many examples utilize TCP/IP both because it is the default protocol for remote clients and because it is the most widely used. No chapter can cover all the possible ways of connecting, but this one is designed to give you the tools you need to get set up right from the start and to navigate your way in case specific issues arise. The first client configuration tool we look at is SSCM (SQL (Structured Query Language) Server Configuration Manager.
Client Configuration Using SSCM
The Client Network Utility available prior to SQL Server 2005 has been decommissioned, and all its functionality is now built into SSCM. This includes the capability to create server aliases, to enable and prioritize network protocols, to control the various SQL Server services, and more.
One thing Microsoft is keen on including in Books Online is that neither Setup nor sqlncli.msi installs the actual network protocols themselves, nor do they enable them at the operating system level. This means that if you do not have TCP/IP installed and you need to start using it, you have to first set it up by using the Network Connections Control Panel applet (if you’re using Windows).
You can launch SSCM directly from its Start menu icon, or you can access it in the Services and Applications node of the Computer Management console. When you have SSCM up and running, to access its client-side functionality, you expand its top-level node (SQL Server Configuration Manager (servername)) and then you click the SQL Native Client 10.0 Configuration node. Below it, you click the Client Protocols node to reveal the enabled state and priority order of each protocol, in grid format, in the right pane (see picture below).
SQL Server 2008 offers a robust client/server architecture that provides speed and security, simple configuration and maintenance, and enhanced management capabilities. This section contains the latest information on how toinstall, configure, and connect to SQL Server 2008 from the client side, and it offers key server-side insights that will help provide a complete understanding of what you need to do establish a database connection.
What’s New in Client Installation and Configuration
Client installation and configuration in SQL Server 2008 is similar to SQL Server 2005 but does have its share of changes. First and foremost is the introduction of a new net-library named SQL Native Client 10.0 (SNAC10). SNAC10 gives applications access to the new features and data types available with SQL Server 2008. It builds on the data access component distribution strategy introduced in SQL Server 2005 that was simply called SQL Native Client (SNAC or SNAC9). The good news is that your applications can continue to access SQL Server 2008 with the older SNAC components. Both SNAC9 and SNAC10 can be used on the same client system. SNAC9 is not able to reference new features in SQL Server 2008, however, so you have to upgrade to SNAC10 to gain access to them. Another big change in SQL Server 2008 is the removal of the Surface Area Configuration (SAC) tool. The SAC tool was introduced in SQL Server 2005 and was a key part of client configuration.
The functionality made available in this tool has now been replaced with Policy-Based Management features and changes in the SQL Server Configuration Manager (SSCM) tool. For example, the option to allow Remote Connections that was available in SAC is no longer there. You should look
One last change in SQL Server 2008 Client Installation and Configuration that may rear its head relates to the BUILTIN\Administrator windows group. By default, this group is no longer included in the SQL Server sysadmin fixed server role on
In past versions of SQL Server, the BUILTIN\Administrator windows group was added to the
Client/Server Networking Considerations
Before we delve into the features on the client side in SQL Server, it’s important to make note of a few server-side features. This information will help you gain an understanding of which networking features are initially configured on the server (after an installation or upgrade) as well as how incoming connections are dealt with. Such knowledge can be invaluable in diagnosing connectivity issues.
If you’ve been following along chapter by chapter, you’ve learned how to install or upgrade an instance of SQL Server 2008.
Server Network Protocols
The first and most basic step after a SQL Server installation or upgrade is to make sure the appropriate network protocols are configured on the server.
Note that the term server is used here to refer to an instance of the SQL Server 2008 Database Engine. The term client is used generally to mean any program that needs to communicate with a server. The server and client may reside on the same physical machine (especially when using SQL Server Mobile and Express Editions).
First, you should ensure that the protocols your clients once used to connect to SQL Server 7, 2000 or 2005 (or that your clients would like to use) are still supported by SQL Server 2008 and configured. You might be surprised to learn that the following protocols that were supported in SQL Server 2000 are not supported by SQL Server 2005 or SQL Server 2008:
If you were using these protocols and you’ve upgraded from SQL Server 2000, your clients are no longer able to connect. Following are the only protocols that SQL Server 2008 supports:
If you were using any of these protocols and you just upgraded, Setup copies your preupgrade settings over to SQL Server 2008, including the enabled state, IP addresses, TCP ports, pipe names, and so on. Clients can simply test their connections to be sure the upgrade was successful, and in most cases, no changes need to be made.
The Shared memory protocol works only for connections both to and from the same machine hosting the Database Engine. Shared memory is used by client tools such as SQL Server Management Studio (SSMS) and SQLCMD, and it’s also a good choice for use
All remote connections to SQL Server are thus disabled by default. Following is an extremely common client-side error message illustrating connection failure due to disabled remote connectivity:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
The exact wording of this message varies slightly, depending on the particular client or connection method used. The same error also occurs when the Database Engine service is stopped. In SQL Server 2008, remote connections must be enabled for each network protocol on which you want the server to communicate.
This is easily accomplished using the SQL Server Configuration Manager (SSCM). You launch SSCM from the SQL Server 2008 Configuration Tools menu group. In SSCM, you expand SQL Server Network Configuration and then select the Protocols entry for the SQL Server instance that you want to configure. In the Details pane, right-click on one of the available protocols (for example, Named Pipes) and select Enable to allow connections for this protocol (see picture below). SSCM serves many purposes and is discussed in detail later in this chapter.
When the protocol is enabled, SQL Server is configured to listen for connections from clients using the same protocol. You must restart the SQL Server instance for the changes to take effect and for SQL Server to actually start listening for connections. You can verify that SQL Server is listening on the protocol that you have enabled by looking at the SQL Server error log. Each time the SQL Server instance is restarted, messages are written to the log indicating which protocols it is listening on. The following sample error log messages show what SQL Server is listening for:
Server is listening on [ ‘any’ <ipv4> 1719]. Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$INST2008\sql\query ].
SQL Server listens on all configured protocols simultaneously, giving no preference or priority to any. This is in contrast to the explicitly prioritized manner in which clients attempt to connect via all configured protocols. The client configuration is discussed in detail later in this chapter.
In SQL Server 2005, the Surface Area Configuration (SAC) tool also could be used to allow remote connections and to configure the protocols on which they communicate. The SAC tool has been removed in SQL Server 2008, so you need to look to the SSCM to configure your protocols.
The Server Endpoint Layer
A networking feature in SQL Server 2008 adds an additional layer to the client/server network structure: Tabular Data Stream (TDS) endpoints. When you install (or upgrade to) SQL Server 2008, a default system endpoint is created on the server for each available protocol on the server. These endpoints cannot be dropped, and they are created regardless of whether the protocol is disabled or otherwise unavailable.
The term endpoint in this context refers to the combination of a protocol selection, one or more IP addresses (or pipe names), and any associated port numbers.
These are the default system endpoints:
You can view these endpoints and check their status by executing the following T-SQL statement:
Use Master GO SELECT *
WHERE principal_id = 1
By default, all users are granted access to these endpoints (except the DAC, which is only for members of the sysadmin role). Administrators can create new endpoints on the server to increase connection security by stopping (or disabling) the default system endpoints and then creating new user-defined endpoints that only specific clients can access. (Creating a new system endpoint automatically revokes permission on the default endpoint of the same protocol to the public group.) Only one named pipe and one shared memory endpoint can exist per instance, but multiple VIA or TCP endpoints (with different port and address settings) can coexist.
Each endpoint communicates with clients via TDS packets, which are formatted on the server side by SNAC and on the client side by SNAC or another of the net-libraries. Administrators have the option of stopping and starting endpoints while sessions are still active, preventing new connections from being made while still supporting existing ones.
An administrator can grant or revoke endpoint access to specific users or groups (for example, preventing backdoor access through client tools). It is therefore important for clients to know that this structure exists and to learn how they receive permission to connect to endpoints through a server-side process known as provisioning.
Client Access Provisioning
There are three fairly straightforward rules of access provisioning. If any one of these rules is met by an incoming client, that client may access the endpoint. If none are met, the client is denied access. These are the rules:
If the endpoint to which access is successfully provisioned is currently stopped, or if the user does not have permission to connect to it, no further endpoints are tried and the client cannot continue.
For example, let’s say a server has three TCP/IP endpoints defined:
A client attempts to connect specifically to 192.168.1.101:91. Because this is an exact address and port match, the client can try to connect to TCP_UserCreated 101_91. Having an exact address and port match meets the first provisioning rule. A second client attempts to connect to any IP address on Port 91. Because there is no exact address match, the client cannot attempt to connect to TCP_UserCreated 101_91. However, the client can attempt to connect to TCP_UserCreated Any_91 because it is configured to listen on all IP addresses. This meets the second provisioning rule. A third client attempts to connect on any port and any address. If TSQL Default TCP is started, the client is granted permission to attempt to connect. This meets the third provisioning rule.
Settings such as IP addresses and TCP ports are used to implicitly connect to specific endpoints. These values are specified by clients in connection strings, data source names (DSNs), and server aliases, all of which are discussed later in this page topic in the “Client Configuration” section.
If, at any time, you want to discover which protocol and endpoint a connected client is currently using, you can run the following T-SQL to list the current connections and related protocols. The session_id identifies the server process ID (SPID), and an additional WHERE clause can be added to the SELECT statement that selects only the SPID you are interested in:
SELECT name, net_transport, session_id, e.endpoint_id
FROM sys.dm_exec_connections d
JOIN sys.endpoints e
ON e.endpoint_id = d.endpoint_id go name net_transport session_id endpoint_id TSQL Local Machine Shared memory 53 2
ON e.endpoint_id = d.endpoint_id
go name net_transport session_id endpoint_id
TSQL Local Machine Shared memory 53 2
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.