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
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.