SqlClient Default Protocol Order

http://blogs.msdn.com/b/adonet/archive/2010/04/18/sqlclient-default-protocol-order.aspx

A while ago, someone came to me with a very interesting problem: basically, his .NET application was taking more than a minute to open a connection. After a quick look and some investigations, we found out that only Named Pipes was enabled on this individual’s SQL Server. Since this is a legitimate configuration, I decided to document this behavior in this post, so that, you can quickly find an answer if you face this problem.

As you know, SqlClient implements native access to SQL Server on top of SQL’s protocol layer. To establish communication between client and server both need to use the same protocol.

By default, SqlClient attempts to make the connection using the following protocol order[1] :

1. Shared Memory
2. TCP/IP
3. Named Pipes

Additional from envykok (refer to http://msdn.microsoft.com/en-us/library/ms187892.aspx)

Shared Memory

---

Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer , it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

TCP/IP

---

TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today. Configuring your computer to use TCP/IP can be complex, but most networked computers are already correctly configured. To configure the TCP/IP settings that are not exposed in SQL Server Configuration Manager, see the Microsoft Windows documentation.

Named Pipes

---

Named Pipes is a protocol developed for local area networks . A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

 

 

Starting from the first one, it moves to the next, failure after failure until reaching a valid one. So, if only Named Pipes is enabled on the server, the client goes through a failed Shared Memory and a failed TCP/IP (with their timeouts) before reaching the right one.

Now, to minimize some performance impact, SqlClient detects if the server name is a remote host, then it’s smart enough to bypass the Shared Memory and goes directly to the next option. Also, if SSRP is required (when you need to connect to a non-default instance, for example), then SqlClient queries the SQL Server Browser in order to gather the appropriate protocol list from the target server.

Now, when connecting the default instance, you can override this default behavior to avoid any timeouts when opening your Named Pipes connection. Basically, in the connection string, you can specify the protocol you wish to use, like the sample below:

Data Source=MyDatabaseServer; Integrated Security=SSPI; Network Library= dbnmpntw

Or, you can just specify a prefix in MyServer, just to tell SqlClient to establish a Named Pipes connection:

Data Source=np:MyDatabaseServer; Integrated Security=SSPI

As a conclusion, it’s a good practice to understand which protocols your DBA enabled in the server and, if you they don’t have plans to use TCP, you need to make sure you explicitly made this call in your connection string.

- Luiz Fernando Santos
   ADO.NET PM