Just had an interesting error the other day.

Got this error when trying to connect one of our servers that has just been patched:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and:
1. ensure Shared Memory protocol is enabled
2. ensure Named Pipes protocol is enabled
3. ensure TCP/IP is enabled, and s ahead of the Named Pipes in the settings

Check out Pinal Dave’s excellent post on this.

All of these are set in our server.

Next up, I checked the log files. Logs are typically located in a folder similar to this (note you will need to change the folder MSSQL.1 with the appropriate instance name’s folder name):
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG

When I checked the logs however, I got yet another interesting error:

Could not connect because the maximum number of ‘1’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: ]

It’s an interesting error because:
1. We just restarted the server and nobody is able to access it yet
2. DAC is disabled

Solution

After some more sleuthing and fiddling around, I went back to SSCM to check ensure the protocols are enabled properly. There’s 4 (four) options – SharedMemory, TCP/IP, Named Pipes and VIA (for Virtual Interface Adapter). Everything is turned on, except for VIA.

I enabled VIA protocol – and that did it. That was the issue.

Learn more about VIA:
http://en.wikipedia.org/wiki/Virtual_Interface_Adapter
http://msdn.microsoft.com/en-us/library/ms191229.aspx