Force Protocol in SQL Server Connection String


We had a situation at our place of work whereby a client of ours was experiencing slow network performance when connecting to a SQL Server 2008 R2 database from a WinForms client. There were three computers that were a particular problem in that they would consistently timeout when the WinForms client was started. Thus, making the application unusable.

The first thing I did that succeeded in the application consistently creating a successful connection to the SQL Server, was to add the Connect Timeout = 0; property to the connection string. This prevented the application from timing out. I also confirmed that the timeout property on the SQL Server was also set to 0.

After some investigation it turned out that the SQL Server was set up to use the protocols of Shared Memory (Priority 1), TCP/IP on the default port of 1433 (Priority 2), and named pipes (Priority 3). In the connection string of .NET applications it is possible to force SQL Server and the client application to use a specific protocol. I did this and found that specifying TCP/IP with and without the port number failed with a network instance exception. But when I forced the use of the named pipes protocol, the application work instantly.

It turns out that if you are connecting to a local SQL Server, the connection will use shared memory. If you are connecting to a remote SQL Server, the connection will attempt to use TCP/IP first. If it fails to connect to the SQL Server using named pipes. However, in this instance the SQL Server was not configured to accept connections using TCP/IP. So on the SQL Server I swapped the priorities of the named pipes (to priority 2) and TCP/IP (to priority 3). The connection string’s server property was changed to force the named pipes protocol: server = np:serverName;. This had a big impact on speed in a positive way.

So to help you with slow network issues when connecting to a SQL Server here are some useful articles:

I used these articles with success. Here are the main points from both these articles.

  • To discover the connection protocol used by SQL Server, create a view on the master database that uses Jon Galloway’s script, and write a utility program that calls that view and displays the properties within the client.
  • In the connection string set the timeout period and force the specific protocol to suit your needs.
Advertisements