To facilitate client-to-server communication in a variety of network architectures, Microsoft provides a choice of network libraries for use based on your network protocols. The next sections examine the different protocols available and how you can customize their use.
Table 9.1 identifies the client- and server-side Net-Libraries for each supported IPC.
|Multiprotocol||DBMSRPCN.DLL||SSMSRP70.DLL||TCP/IP, NetBEUI, NWLink|
|Named Pipes||DBNMPNTW.DLL||SSNMPN70.DLL||TCP/IP, NetBEUI, NWLink|
|Banyan Vines||DBMSVINN.DLL||SSMSVI70.DLL||Banyan Vines|
In the case of Multiprotocol, named pipes, AppleTalk, Banyan Vines, and VIA GigaNet SAN, the DBNETLIB.DLL on the client and server routes the communications to the libraries listed.
Using the Client Network Utility, you can choose the specific network library you want to use to make connections to your SQL servers. All the network libraries are implemented as dynamic link libraries (DLLs) and are used during network communication using specific InterProcess Communication mechanisms.
With distributed computing?of which a client connection to a server is the most fundamental?a connection is made at a process-to-process level to allow communication in both directions. The foundation for this approach is an InterProcess Communication mechanism?of which Windows Sockets, Remote Procedure Calls (RPCs), NetBIOS, named pipes, and mailslots are all examples.
The following sections detail each of the available network libraries.
Named pipes has historically been the default network library used by SQL Server running on NT, but it has been replaced by TCP/IP. By default, named pipes is installed to listen on a standard pipe of \\.\pipe\sql\query. This pipe name can be changed after installation to make it more difficult for malicious attacks against publicly accessible SQL servers.
If you do change the server's default pipe, you must use the General tab in the Client Network dialog box to connect to the new pipe name. To set up the new pipe, click the Add button on the Client Network Utility and enter the server's name and the new pipe name.
Under Windows 95, the 32-bit named pipe network library does not support server enumeration. This is the mechanism used to create a list of available servers to which you can connect.
TCP/IP Sockets is the new default protocol that allows communication over the TCP/IP protocol using standard Windows Sockets as the IPC mechanism. Using TCP/IP, the SQL Server service is assigned to port 1433; this is the official Internet Assigned Number Authority (IANA) socket number. If this value is changed on the server, you must set up a specific entry using the Client Network Utility to indicate the modified port number.
For information on connecting to a SQL server hidden behind a proxy server, see "Connecting to SQL Server Through Microsoft Proxy Server," later in this chapter.
The Multiprotocol network library allows communication over supported Windows NT IPC mechanisms. Currently, Microsoft considers only named pipes, TCP/IP sockets, and NWLink IPX/SPX to be tested and supported. The RPC service receives the server's name from the Multiprotocol network library; based on the network protocols available on the client, RPC attempts to use each protocol in turn until a connection is successfully made. The client must be using a Net-Library and protocol stack that corresponds to one on which the SQL server is listening. A client connecting with the Multiprotocol Net-Library over TCP/IP can connect to a SQL server that is running the TCP/IP protocol. If the server were running the IPX/SPX protocol stack, then no connection could be made.
Among the protocols that RPC supports are Windows-based Novell clients using SPX or IPXODI.
Multiprotocol allows authentication by Windows NT over all supported RPC protocols. Prior to SQL Server 2000, Multiprotocol was one of the only ways to get encryption over your network connection. Now, Secure Sockets Layer (SSL) encryption can be enabled over any Net-Library in SQL Server 2000 via the Server Network Utility.
The server's address is determined in the same manner as it is for named pipes: by the server's name. Multiprotocol does not support server enumeration from the client side; servers listening on just Multiprotocol do not appear in browse lists on clients able to enumerate available servers.
To map a computer name to a physical node, the RPC service makes use of the network protocol's naming service: WINS for TCP/IP, SAP for NWLink IPX/SPX, and NetBIOS broadcasts for named pipes.
Novell SPX?based clients require the NWLink IPX/SPX protocol. In a default installation of SQL Server, the service name is that of the computer. If this default is changed, you must make a customized entry for that server for each client.
The Client Network Utility allows the specification of the following items:
Network address, consisting of the following:
Your network administrator can provide this information for you.
To allow Apple Macintosh clients to use their native AppleTalk network protocol, the AppleTalk ADSP is the network library of choice.
Macintosh clients can also communicate using TCP/IP.
Custom AppleTalk entries allow you to specify the object name and zone of the SQL server within the AppleTalk network.
The network library used to communicate over Banyan Vines' IP network protocol is the Banyan Vines Sequenced Packet Protocol (SPP).
When a SQL server is installed to listen on Banyan Vines, it is assigned an address of the form servicename@group@org. This address is what you must specify when setting up the client.
Banyan Vines is currently available only on Intel-based machines.
A new Net-Library based on the Virtual Interface Architecture (VIA) was added with SQL Server 2000. It is used for fast and highly reliable data transfer between servers that are located in the same Datacenter. For VIA to work, it must be run on supported hardware provided by Giganet.
Setting up your clients to connect to your SQL servers is not always as simple as installing the protocols on the machine. You might have to make modifications to the default settings for a protocol (as detailed in the previous sections). You might also have some additional steps or problems to overcome.
You should troubleshoot client connection problems in the same way you tackle other problems: methodically and carefully. You must first consider the different elements involved. Then isolate them, test them, and eliminate them as the source of the problem. You need to consider the following elements:
Client hardware?Network interface card (NIC), network cable
Network hardware?Routers, bridges, WAN links
Server hardware?Network interface card, network cable
Client software?Operating system, other applications, network protocol, network library, database library, SQL application, login, password
Server software?Operating system, network protocol, network libraries, SQL Server configuration
Some of these items are obvious and easily identified: Is the cable plugged into the card/wall? Others require more work and other tools. Some hardware problems such as troublesome network architecture components require more sophisticated steps.
The first step is to determine whether you can see the server's name in a network browse list (look in the Network Neighborhood or use the net view command). If you cannot see the server, you might have a router or incompatible network protocol problem. For clients that are running TCP/IP, ping is another good starting point for determining if the client machine can connect to the server. This is discussed in more detail in the "Troubleshooting TCP/IP Connections" section that follows.
If you can see the server, then the next step is to try to make a network connection to a share on the SQL server. You can attempt to read and write a file to that location. This will establish what type of network permissions you have on the server. In some scenarios, you can still connect to SQL Server if you are unable to read and write a file, but this information is still valuable in diagnosing your problem.
Finally, you can try a different protocol from the client. If you can connect with one protocol but not another, you have eliminated at least some of the potential problem areas.
The simplest way to check the visibility of a server is to use the ping utility, which has this form:
You can use the name of the server if it can be identified by a DNS, WINS, or HOSTS file. If ping is successful, you should receive some feedback on the time it takes for a packet to bounce back from the server. If the server cannot be found, you receive an error message.
PathPing is another useful utility shipped with Windows 2000 that is similar to the ping command. It has similar syntax to that of ping, but it has some advanced features including the ability to see the names of computers that are connected on the way to the destination server. A simple example follows:
pathping dbsvr7_test Tracing route to dbsvr7_test [00.00.00.01] over a maximum of 30 hops: 0 win2000svr [00.00.00.00] 1 DBSVR7_TEST [00.00.00.01] .....
This example shows partial output from the pathping command run from a Windows 2000 server named win2000svr to an NT 4.0 server named DBSVR7_TEST. The output shows the names of the servers for each network hop along with the associated IP address. In this case, only one hop was required to get between the two servers, but if additional hops are necessary, then they will be displayed as well, up to a maximum of 30.
The Microsoft Data Access Components (MDAC) version 2.6 that is installed as part of SQL Server 2000 has a documented bug related to unusually slow connection times when using an IP address. For instance, if you have a TCP/IP alias set up for your database server with a specific IP address, you might find that connections to the server timeout take unusually long. This problem was corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2. Refer to the Microsoft Knowledge Base article Q300420 for more details.
Finally, the data found in the sysprocesses system table can be useful when diagnosing TCP/IP and other network connectivity to the database server. This table contains a myriad of information, including the network library that is being used for active connections to the server. This can be helpful in circumstances where some users are able to connect to the server but others are not. You can determine if those users that are connected are using a common protocol, and if so, determine if the users that can't connect have the same client setup.
The troubleshooting discussions in this chapter have focused on resolving network connections to SQL Server. If you are experiencing login failures, you can use a security-oriented tool to diagnose your problem. The SQL Server Enterprise Manager can be used to display security information, and the SQL Profiler can be used to audit logins and login failures.
You can test a network connection over named pipes with a couple different approaches. The first approach checks whether you can see a server and its resources and makes use of the net view command. To check the connection, enter the following statement in a command window:
net view \\servername
If this command is successful, you will see a list of resources. The next thing to try is to connect to the server's named pipe using the net use command:
net use \\servername\IPC$
If you can open a connection in this way but still get a failure when trying to connect a SQL tool, you can use the makepipe and readpipe command-line utilities. On the server, enter the following command:
On the client, enter this command:
readpipe /Sservername /Dstring
In this syntax, string is anything you want. If the string contains a space, the string must be enclosed in quotation marks. The server process creates and manages the pipe. Then the client uses DosOpen to connect and then executes DosWrite to the pipe. After that, the server (hopefully) does a DosRead to receive the value sent. If the connection is a success, you should see results similar to these on the client:
SvrName:\\win2000svr PIPE :\\win2000svr\pipe\abc DATA :test Data Sent: 1 : test Data Read: 1 : test
The server also reacts to the readpipe call with the makepipe utility, showing something like this:
Waiting for client to send... 1 Data Read: test Waiting for client to send... 2 Pipe closed Waiting for Client to Connect...
If you do not get this result, then network named pipe services are probably not loaded. You must install them before clients can make named pipe connections.
The readpipe and makepipe utilities are not installed as part of the SQL Server 2000 installation, but they can be copied from the installation disc.