SQL Server Shared Memory communication protocol usage

Question

I read a lot of articles and answers of similar topics over the internet but didn't find the reason for my problem.

I have a .NET 4 Windows Service connecting to SQL Server Express on the same machine. Only one time in production it happened to see the following error:

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)".

The reason for having this error is clear if we use named pipes protocol because we haven't enabled them on the server. But my question here is why named pipes protocol could be used here? In the connection string we have used "Data Source=.\SQLEXPRESS". Having in mind the information in msdn and over the internet my understanding is that when "." is used always the "Shared Memory Protocol" must be used. So what must happen so that ADO.NET SqlClient decides to use Named Pipe Protocol instead?

Most of the times everything is working just fine, but I need to know the process so that we can prevent any further errors of this type if needed.

Thanks in advance, Todor

Answer 1

You should look on your server at sys.dm_exec_connections and see what net transport is actually being used by your clients.

Normally the client tries SM if local server, then tries NP and then TCP. Attempting to connect to (local) follow the same rules, if it wouldn't then tools and apps could not connect to a local server if the server does not listen on SM protocol! The fact that you see errors indicating the client attempted NP points to situations when the SM attempt failed, which could happen due to a busy server, or if the server was restarting or something similar. The system event log and the ERRORLOG may contain more information at the time when this problem occurred.

You can enforce the client to use a specific protocol by modifying the connection string, as per How to use the server name parameter in a connection string to specify the client network library, use servername=lpc:.\SQLEXPRESS to force Shared Memory only.