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."
The above error is obvious when you have setup the SQL Server instance to accept only 1 connection. The default setting for number of user connections is '0' which accepts unlimited connections.
With similar settings one of my friend have had issues and called me for a help, as the setup was SQL Server 2005 for use with ASP.NET 2.0 WebParts. They do not have any SQLExpress installations as it is by default within such instances. So the problem the relevant SQL 2005 instance has been restarted last night and the application is unable to connect the database and also not accepting any other connections by throwing the 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."
To get this error you can read the SQL Server error log from the installation path (which is a default) located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG in a default installation. As per my previous resolution I have suggested to stop all the web and application servers and restart the SQL Server services again. Also asked the friend to connect to SQL instance immediately when the service is restarted, ok the connection is accepted via Query editor. Then suggested to run the following piece of code in order to accept unlimited connection which is desirable:
sp_configure 'show advanced options', 1;
go
reconfigure
go
sp_configure 'user connections', 0
go
reconfigure
go
Also ensure to check the relevant network protocols are enabled on Server side using SQL Configuration Manager and if Dynamic ports are used ensure to mention the same in the connection string. If you have firewall involvement in the network then ensure the relevant SQL ports are kept open and do have exception. Ok, still they are unable to connect to this SQL instance inspite of performing the change and even after a restart again the same error is reported.
I have asked whether they have any Anti-Virus installed on that SQL Server?
Bingo! yes they have got Anti Virus installed as it is faced to internet that too behind a firewall. As the system was rebooted last time the Anti Virus has been taking over the SQL Server service and not allowing any further connections even though after running the above piece of code it will not allow any further connections other than the Anti Virus service. So you have to stop or atleast exclude the anti-virus scan on SQL Binaries and data files directories to avoid such issues.