Named pipe in SQL Server and Its Usage

1. What is a named pipe?

Like TCP/IP (transmission control protocol or internet Protocol), a named pipe is a communication protocol. It is generally used in Lan, because it requires the client to have the permission to access server resources.

To answer this question, I 'd better extract official Microsoft documents.

Http://msdn.microsoft.com/zh-cn/library/ms187892.aspx

To connect to the SQL Server database engine, you must enable the network protocol. Microsoft SQL Server can process requests through multiple protocols at the same time. The client connects to SQL Server using a single protocol. If the client program does not know which Protocol SQL Server is listening for, you can configure the client to try multiple protocols in sequence. Use the SQL Server Configuration Manager to enable, disable, and configure network protocols.

Shared Memory

Shared Memory is the simplest protocol available, with no configurable settings. Because clients using the Shared Memory Protocol can only connect to SQL Server instances running on the same computer, it is useless for most database activities. If you suspect that other protocols are incorrectly configured, use the Shared Memory protocol for troubleshooting.

Note:
Clients Using MDAC 2.8 or earlier versions cannot use the Shared Memory protocol. If these clients try to use it, they will automatically switch to the Named Pipes protocol.

TCP/IP

TCP/IP is a common protocol widely used on the Internet. It communicates with computers with different hardware structures and operating systems in the Interconnect Network. TCP/IP includes the network traffic standard and provides advanced security functions. It is currently the most commonly used protocol in business. It may be complicated to configure a computer to use TCP/IP, but most networked computers are correctly configured. To configure TCP/IP settings that do not appear in the SQL Server Configuration Manager, see the Microsoft Windows documentation.

Named Pipes

Named Pipes is a protocol developed for LAN. A part of the memory is used by a process to transmit information to another process. Therefore, the output of a process is the input of another process. The second process can be local (on the same computer as the first process) or remote (on a networked computer ).

VIA

The virtual interface adapter (VIA) protocol is used together with the VIA hardware. For information about how to use VIA, contact the hardware supplier.

Named Pipes and TCP/IP socket

In a fast LAN environment, the transmission control protocol or Internet Protocol (TCP/IP) Socket Client and the Named Pipes client have the same performance. However, the slower the network speed [for example, on a WAN or dial-up network], the more obvious the performance difference between the TCP/IP Socket Client and the Named Pipes client. This is because the mechanism of inter-process communication (IPC) has different communication modes among peers.

For Named Pipes, network communication is generally more interactive. A peer sends data only when the other peer uses the READ command to request data. Before reading data, the network generally reads a series of information that looks at Named Pipes. This may cause a high overhead in the slow network and cause excessive network traffic. Other network clients will also be affected.

It is also important to clarify whether the local or network pipeline is discussed. If the Server application runs locally on the computer that runs the SQL Server instance, you can select the local Named Pipes protocol. Local Named Pipes runs in kernel mode and is fast.

For TCP/IP sockets, data transmission is more efficient with less overhead. Data transmission can also take advantage of the performance enhancement mechanism of TCP/IP sockets, such as window setting and delay confirmation. This may be useful in slow networks. For different types of applications, such performance differences may be very large.

TCP/IP socket also supports a backlog of queues. When trying to connect to SQL Server, this queue can bring limited stability compared to Named Pipes that may cause a busy pipeline error.

Generally, TCP/IP works better in a slow LAN, WAN, or dial-up network. When the network speed is not a problem, Named Pipes is a better choice, because it is more powerful, easier to use, and has more configuration options.

Enable Protocol

This protocol must be enabled on both the client and server to work properly. The server can listen to all enabled Protocol requests at the same time. The client computer can select a protocol or try these protocols in the order listed in the SQL Server Configuration Manager.

Note:
Microsoft SQL Server does not support Banyan VINES sequence packet protocol (SPP), multi-protocol, AppleTalk, or NWLink IPX/SPX network protocol. In the past, clients connected using these protocols must select other protocols to connect to SQL Server.

 

In fact, the named pipe looks like the following in the SQL Server Configuration Manager. In fact, an MPS queue name is automatically generated after SQL Server is installed. You can also modify the name.

2. Why use the named pipe?

There are two reasons for using named pipelines:

• Increase speed
• Assuming that the domain name is also in the LAN, the use of the named pipe protocol is faster than the TCP/IP protocol.
• Increase security
• Because the named pipe can only be used for LAN, if the server disables the TCP/IP protocol and only enables the named pipe, some security risks can be avoided.

3. How to use a named pipe?

To connect to SQL Server using a named pipe, the approximate syntax of the connection string in the client code is as follows:

"Server =.; database = northwind; uid = sa; pwd = pass @ word;Network Library = dbnmpntw"

If the server is named

"Server =. \ instanceName; database = northwind; uid = sa; pwd = pass @ word;Network Library = dbnmpntw"

[Note] the named pipe protocol must be enabled on the server and the Broswer service must be started.

 

To avoid the hassle of connecting strings, and to avoid leaking some confidential information about our connection data. SQL Server Native Client also supports alias Definition

Click "alias" and then "create alias"

If you have created an alias, you can directly write it in the connection string.

"Server = Myserver; database = master; integrated security = true ;"

It looks like we used to define an ODBC data source, isn't it?

If the connection string is leaked, it is impossible for someone with ulterior motives to guess what the name of our server is and what protocol is used for connection.

In turn, I thought that this alias was created on the client. So do we have to go to the client to create it ourselves? Obviously not possible. So what should we do?

I naturally think of dynamic creation of ODBC data sources. In fact, we modified the registry at that time. I found the following registry key in the same way.

That is to say, you can dynamically add some items under this project when installing the program. You can achieve it.

 

 

Other reference resources: SQL Server 2008 Native Client Programming

Http://msdn.microsoft.com/zh-cn/library/ms130892.aspx