Partilhar via

SqlClient Default Protocol Order

  • Artigo

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

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

[1] The protocol order is not affected by Configuration Manager, since this tool is intended only the SQL Server Native Client.

Comments

  • Anonymous
    April 28, 2010
    test of auto-comment routing

  • Anonymous
    July 09, 2010
    Thank you!!!!!  I have been struggling with a connection timeout issue for the last week and adding the "np:" solved it.  I'm not sure what I need to do to get the server configured properly so I don't need it, but you gave me a simple bridge.