SQL Server connectivity Issues-Named pipes

Original: SQL Server connectivity issue-Named pipes

Originating From: http://blogs.msdn.com/b/apgcdsd/archive/2011/01/12/sql-server-1.aspx

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].

Four. Named pipe configuration for clients

In most cases you do not need to configure the client because the Named Pipes protocol is enabled by default. However, since you are checking the connection problem for a named pipe, you still need to be familiar with the client's named pipe configuration.

1. Using the Client Network Utility

Clients can use the Client Network Utility to configure the protocols that are used to connect to SQL Server. Run Cliconfg.exe or from the start-up program-->microsoft SQL Server--Client Network Utility to bring up the tool (see Figure 5)

(Figure 5: Using the Client Network Utility)

The left side of the client Network Utility is a disabled protocol, and the right is the protocol that is enabled. If there is no named pipe protocol on the right, click the Enable button to enable it. The protocol up and down order on the right-hand list determines the priority order of client protocols that client-side net-library (Dbnetlib.dll) considers using when connecting to the SQL Server instance. You can change it.

Select the Named pipe click Properties button to view the default named pipe name for the client connection.

The default is Sql\query (see Figure 6). It is important to note that the name is incomplete, and the client Super Socket network library automatically complements the full \\RemoteComputerName\pipe\sql\query format when connected.

If there is a problem with a named pipe connection, you must ensure that the named pipe name that SQL Server listens on is consistent with the default pipe name of the client connection.

(Figure 6: Default named pipe name for client connections)

2. Using SQL Server Configuration Manager

We can also configure the Client network protocol for SQL Server Native client This database driver by SQL Server Configuration Manager.

We open SQL Server configuration Manager and expand the SQL Native Client configuration (or SQL Native client 10.0 Configuration) node in the left panel. and select the client protocols under the node. At this point in the right panel you will see all the supported client protocols. We can start and disable the Named Pipes protocol by right-clicking. You can right-select a property to edit the default pipe for the client. See Figure 7.

(Fig. 7)

3. Use client SQL Server aliases

You can configure the SQL Server alias on the client to explicitly specify the protocol that is used to connect to SQL Server. In some cases, SQL Server listens for named pipes or IP addresses, ports, and so on, which may not be the default, but for some purpose has been changed. In this case, you can use the client SQL Server alias to resolve the connection problem. The Client Network Utility makes it easy to create, delete, or modify SQL Server aliases (see figure two below):

(Fig. 8)

Note the pipe name must match the name of the named pipe that is listening on the server, and the string that is filled in server name must be the correct SQL Server server name. The server alias is filled with any valid string of names, not necessarily the same as the SQL Server server name.

The configuration steps inside SQL Server Config Manager are similar to the above. (Fig. 9)

(Fig. 9)

Five. Troubleshooting steps for Named pipe connection problems

Well, introduce here you should be very familiar with the naming pipeline, it should also be time to discuss the solution of the name pipe connection problem:

Step 1: Use the server-side Network Utility to check the named pipe configuration and confirm that SQL Server has been listening on the named pipe protocol.

Step 2: Use the Client Network Utility to check the client's Connection Agreement configuration to ensure that named pipes are enabled. Of course, the default pipe name for client connections needs to be consistent with the SQL Server listener. In addition, you need to carefully review the SQL Server alias that is not an error.

Step 3: Check network connectivity. For example, to ensure that you can ping not only the IP address of the SQL Server server, but also

The name of the SQL Server server that can be ping.

Step 4: Check whether the client is capable of Windows authentication through the SQL Server server. You can do this using the following command:

NET VIEW \\ServerName

NET use \\servername\IPC$

If these two commands are faulted, there is a problem accessing the SQL Server server permissions. You need to clean up these issues before you solve the problem of named pipe connections.

Step 5: Make sure that the client login (login) account has access to SQL Server. In order to simplify the problem and troubleshoot Windows authentication issues, in this type of problem analysis, if SQL Server is configured with SQL authentication, it is recommended that you use a SQL Server account. When the SQL Server account can be connected, then use the Windows account.

If the five steps above do not solve the connection problem for a named pipe, you can test the named pipe using the utilities described below.

Six. To test a named pipe connection utility

The SQL Server installation disk has utilities makepipe and readpipe that test the integrity of the network named Pipes service. These two gadgets need to be used together. The utility can be found under the X:\x86\Binn directory on the SQL Server 2000 CD. Here's how to use it:

1) at the server's operating system command prompt, type:

Makepipe/pmytestpipe

Note that the/P and subsequent pipename cannot have spaces

This creates a named pipe \\.\pipe\mytestpipe.

After you run the command, the following information is returned:

Making Pipe:\\.\pipe\mytestpipe

Read to write delay (seconds): 0

Waiting for Client to Connect ...

Waiting for client to send ... 1

Data Read:

Hello World

(See Figure 10)

(Figure 10: Using the makepipe utility)

2) at the operating system command prompt at the client workstation, type:

readpipe/smyServer/ D "Hello World" /pmytestpipe

where "Hello World" is the test string. If the string contains spaces, you must enclose it in double quotation marks. No space between /S and server name, no space between/D and string no spaces between/p and Pipename

If a named pipe connection can be established, the client workstations return the following information, respectively:

Svrname:\\myserver

PIPE: \\myserver\pipe\mytestpipe

Data:hello World

Data Sent:1: Hello World

Data Read:1: Hello World

(See Figure 11)

(Figure 11: Using the Readpipe utility)

If the readpipe and makepipe tools are not connected, the network named Pipes service is not available. You'll need to check for network connectivity issues or permissions to access Windows first.

Seven. Some common connectivity issues

connection Problem One :

[Named Pipes] SQL Server does not exist or access denied.

[Named Pipes] ConnectionOpen (Connect ()).

This connection error is due to the fact that the client did not find a named pipe server (that is, SQL Server).

Workaround:

1) Check the network connectivity, such as Ping, and verify that SQL Server is started.

2) Check the SQL Server server side and client's named pipe configuration.

Connection problem Two:

Login failed for user ' NULL ' or Login failed for user anonymous

This error basically means there is no problem with network connectivity, just using a named pipe to access the server has permissions on the issue. Don't forget that ipc$ share oh. Named pipes cannot be used without permission to access ipc$. You can run the "net use \\servername\IPC$" command to test it.

In most cases this error occurs because you are using a less privileged account to log on to the client machine and you are using Windows Authentication to access SQL Server. Let's say you're logged on with a local account on the client machine and use Windows Authentication to access SQL Server. Because the client local account does not have access to the server's resources, it will inevitably result in access failure. The workaround is to use the domain's account to re-login to the client machine, while ensuring that the domain account has been added to the SQL Server login. Another way to do this is to use the SQL Server account instead of the Windows account to try to connect to SQL Server and use the TCP/IP protocol. Do not forget that even with SQL account, if the protocol is named pipe, Windows Authentication is also required.

Connection problem Three:

Login failed for user ' User123 '.

Many of these problems are User123 resources that do not have access to the server, or do not have access to SQL Server. It should not be a connectivity issue but a SQL Server access (that is, authentication for SQL Server) issue J

Eight. Tips (Tips)

1. After the connection is established, how can I view the protocols used? You can run the following statement in SQL Server 2000 Query Analyzer or SQL Server 2005/2008 Management Studio:

Select net_library, hostname, program_name, Nt_domain, Nt_username, loginame from

Master.. sysprocesses where spid>50

The Net_Library field describes the protocol used by the connection. Note If you see that the protocol is LPC, then it represents the connection using the shared memory.

2. When you use a SQL Server client connectivity component from SQL Server 7.0 or earlier versions, you must use the Client Network Utility to set the alias before connecting to a named instance of SQL Server 2000. Another method is to install MDAC 2.6 or later MD AC version. Only MDAC2.6 or later will be able to connect to a named instance of SQL Server 2000 without a client alias.

3. In addition to using SQL Server Query Analyzer to test connectivity issues with SQL Server, another better test tool is the ODBC database source. Run ODBCAD32.exe to bring up the tool. Use this tool to attempt to establish a System DSN that is connected to SQL Server. Why is it better to say ODBCAD32.exe tools? Because it outputs more information than Query Analyzer.

For example, to connect to a nonexistent server, the information that is output from Query Analyzer is:

And if an ODBC data source establishes an ODBC DSN to test, the output information is:

This information not only indicates the protocol used by the connection, but also shows an important error number such as 53.

If you use NET helpmsg to view error number 53, you will find:

Net helpmsg 53

The network path is not found.

Since it is the network path not found, it is obvious that the connectivity problem is.

4. Named pipes can also be said to be based on the naming file system NPFS (Named Pipe file systems) to achieve, related drivers have npfs.sys and so on. Interested readers can do their own research.

5. Finally post a communication component diagram from the SQL Server online manual that you would like to be helpful in understanding SQL Server connectivity issues:

SQL Server connectivity Issues-Named pipes