SQL Server Connectivity Issues Bible-Named pipes

SQL Server Connectivity Issues Bible-Named pipes (1) APGC DSD Team12 Jan 1:24 AM

• 3

One. Objective

In the process of using SQL Server, the most users encounter is the connection problem. This article will discuss all aspects of SQL Server connectivity issues in depth, and hopefully will help you resolve your SQL Server connectivity issues completely.

SQL Server supports a number of communication protocols, such as Named Pipes (Named Pipes), TCP/IP sockets, shared memory, and so on. Because the most commonly used are the first two named pipes and TCP/IP sockets, we mainly discuss the connection problems of the two types of protocols. Let's talk about named Pipes first, and then we'll talk about TCP/IP.

two. What is a named pipe (Named Pipes)

In Windows systems, interprocess communication mechanisms include postal slots, pipelines, sockets, and so on. On the Windows platform for pipelines, there are two types of named pipes and anonymous pipelines. Named pipes communicate through interprocess communication (IPC) mechanisms that enable one-way or two-way data communication between different processes on the same computer, or between processes that span a network of different computers. Specifically, named Pipes are built on the server's ipc$ share and communicate through ipc$ sharing. The name of the pipeline is also similar to a network share, both in UNC format (see the named pipe naming format discussed later).

If you've ever programmed using named pipes, you'll find that the command pipeline uses standard WIN32 file system API functions such as ReadFile and WriteFile to send and receive data, regardless of the system's base-level network Transfer Protocol (such as Tcp,netbeui or IPX). The basic process for a client to use a named pipe connection is as follows:

(1) The SQL Server server uses the CreateNamedPipe function to create a named pipe and listen to it.

(2) The client uses the CreateFile () and WriteFile () functions to attempt to connect to the server's named pipe.

Sum up

1) Named pipe is not a basic network protocol

Because Named Pipes run on the base protocols such as TCP,NETBEUI, they are not a primary network transport protocol, so even if you use named pipe communication between the client and the SQL Server server, You also need to configure TCP or other grassroots network protocols to ensure network connectivity between the client and the SQL Server.

2) Named pipe is a protocol that requires system authentication

When a client connects using a named pipe protocol, it first accesses the server's ipc$ share. Access to the ipc$ share must be authenticated through windows. With this authentication, you can actually connect to the SQL Server Listener's pipeline. So named Pipes is a protocol that requires Windows authentication. This is one of the biggest benefits of using named Pipes, which allows you to take advantage of the security mechanisms built into Windows Server directly. If you do not have access to the file system of the SQL Server server (Permission), you cannot access SQL Server by using named pipes.

three. how SQL Sever Named Pipes works

SQL Server first creates a named pipe on the server and listens to it, and then the client can connect to the pipeline for a conversation. For each client pipeline connection request, SQL Server creates a new pipeline instance to communicate with.

1. Name of the named pipe.

SQL Server identifies named pipes in UNC format, just as with other Windows programs. For example:

\\server\Pipe\path_name

The named pipe identification string above can be divided into three parts

\\server section: Specifies the name of the server on which the named pipe resides. Named pipes are created on this machine. SQL Server mostly uses a dot (.) to represent the local server it is running on.

\pipe part: A fixed "hard-coded" string (both uppercase and lowercase) indicates a pipeline protocol.

\path_name part: Name of the pipe named. Can be a multilevel directory. SQL Server listens for a level two directory that is \sql\query.

By default, the names of the named pipes that SQL Server listens on are as follows:

(the default instance and named instance have different pipe names)

Default instance: \\.\pipe\sql\query

Named instance: \\.\pipe\mssql$instancename\sql\query

The pipe name above can be changed using the Server Network Utility after installing SQL server.

2. Configure or view Named Pipes for SQL Server 2000 snooping

Run the Svrnetcn.exe Call Server Network Utility on a SQL Server 2000 Server (the English name is SQL Server networking Utility), or on the Start menu, point to Programs, and then point to Microsoft SQL Server, and then click Servers Network Utility. If no named pipes are in the enabled protocols (enabled protocals) box, select the named pipe from the left and click the enable button to join (see Figure 1). Select the Named pipe protocol and click the Properties button to view or modify the named pipe name that SQL Server listens on (see Figure 2). If you just enabled the Named pipes protocol then you need to click the OK button and restart the SQL Server service to take effect.

(Figure 1: Server Network Utility)

(Figure 2: Named pipe name)

3. Configure or view named pipes for SQL Server 2005/sql Server 2008 snooping

Open SQL Server Configuration Manager, expand the SQL Server Network configuration node in the left panel, and select the SQL Server instance below the node that you want to manipulate. At this point, on the right panel, you see whether the individual network protocols for the instance are enabled, including Named pipes. On the right side of the protocol name, it shows whether its current status is enabled. Refer to Figure 3.

(Figure 3 SQL Server Configuration Manager)

Right-click Named Pipes and select enable . Then restart the SQL Server instance to make the action you just made effective.

In Figure 3, you can view or modify the named pipe name that SQL Server listens on by selecting the Named pipe protocol and clicking the Properties button.

(Fig. 4)

4. Verify that SQL Server is actually listening on the named pipe

An important step in resolving a named pipe connection problem is to check if SQL Server is actually listening on the named pipe.

In SQL Server 2000, in order to verify that SQL Server is indeed listening on a named pipe, you can open SQL Server Query Analyzer, and then run the following command:

EXEC master. Xp_readerrorlog

In the result bar, if you see the following line, SQL Server has been listening on named pipes:

2000-01-13 09:57:37.38 Server SQL Server listening on TCP, Shared Memory, Named Pipes.

In SQL Server 2005 or SQL Server 2008, we can open SQL Server Management Studio and then run:

EXEC master. Xp_readerrorlog

You can see the pipe name it listens to in the results bar:

2011-01-12 11:35:37.980 Server server named pipe provider is ready to accept connection on [\\.\pipe\sql\query].

SQL Server Connectivity Issues Bible-Named pipes