Applies To: Tested on SQL Server 2008, 2008 R2, 2012 and 2014.
Issue:
In this blog, I would like discuss about one of most commonly faced issues that you may encounter when connecting to the SQL Server. When you try to connect SQL Server you may get the error below. The error can occur while connecting to SQL Server from any custom application and also from SQL Server Management Studio (SSMS). You may not be able to connect to the SQL Server locally as well.
Error:
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).
If you go to SQL Server machine and check for the SQL Server Error logs (default location: C:\Program Files\Microsoft SQL Server\MSSQLXX.<InstanceName>\MSSQL\Log) you will the below error message.
Error:
2015-07-07 14:19:14.82 Logon 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: XX.XX.XX.XX]
Cause:
The above error message from SQL Server Error log says that the SQL Server is configured to accept a maximum of 1 active connection.
Resolution:
In order to solve the issue you have to close all the existing active connections and change maximum concurrent connections to infinite from command prompt.
Steps:
1. Close all concurrent SQL Server connections from command prompt. To do this, run the command below from command prompt window (cmd).
"C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -mSQLCMD –c
(The above command is for the default instance of the SQL Server (MSSQLSERVER), for a named instance of SQL Server provide the name of the instance after the –s switch. Also this is default path of sqlservr.exe. If you have installed SQL Server in a different path, use that path instead. )
2. Open another command prompt (cmd) as an administrator and execute the command below to connect SQL Server.
sqlcmd -E
(In the above command, we are connecting to a default instance of SQL Server using windows authentication. Please check the below article for the other available switches)
Use the sqlcmd Utility: https://msdn.microsoft.com/en-us/library/ms180944(v=sql.120).aspx
3. Once connected to SQL Server, execute the below command to change the number of concurrent sql connection to 0 (0 means infinite no. of connections).
sp_configure 'show advanced options', 1; (hit enter)
go (hit enter)
reconfigure (hit enter)
go (hit enter)
sp_configure 'user connections', 0 (hit enter)
go (hit enter)
reconfigure (hit enter)
go (hit enter)
Exit (hit enter)
4. Exit the SQL command prompt.
5. Restarted the SQL Server Service.
6. Try connecting to SQL Server.
Note: You can also change the number of concurrent SQL Server connection from the SQL Server Management Studio (SSMS) (Properties->Connections->Maximum number of concurrent connections), but in my case since the connection from SSMS was not successful, we made the changes using command prompt.
Please drop in your comments or connect with Microsoft BI-ONE CSS team if you are still encountering the same issue even after performing the above steps.