Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

5 days ago I was access able to access SQL Server on my localhost machine via SSMS perfectly, no errors logging in with credentials. Now I get this error:

================================================================================== A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)


The system cannot find the file

End Error Message =========================================================================

  • I have not changed any configuration to my knowledge regarding my database or SSMS.
  • My default services are running with the same user account that was working before and has proper permissions to run the service. My instance name is: MSSQLSERVER. Services running with Automatic Startup:
    • SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
    • SQL Server (MSSQLSERVER)
    • SQL Server Analysis Services (MSSQLSERVER)
    • SQL Server Integration Services 11.0
    • SQL Server Reporting Services (MSSQLSERVER)
    • SQL Server VSS Writer
  • Here is the last log entry located in: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
    • 2013-02-12 08:16:48.32 Logon
      Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.58.1]

How can I increase my number of connections to access SQL when I can't access SSMS or SQLCmd to execute sp_configure or right click on my database and edit properties to increase the max connections allowed? Or is it another issue? Currently I have named pipes and Shared Memory Client protocols disabled, only tcp/ip is enabled. With all enabled I get the same exact error. I've tried with my firewall on and off, same issue with all client protocols enabled.

Thanks in advance, -Lane

share|improve this question
add comment

2 Answers

Thanks to squillman's answer, along with these few more steps I fixed my issue:

  1. Disabled all services besides SQL Server (MSSQLSERVER)
  2. Reboot your machine (most likely restarting SQL Server (MSSQLSERVER) service would have worked also)
  3. I was now able to log into SSMS, but was crashing when trying to right click the instance and go to properties.
  4. Exit SSMS, run cmd and type sqlcmd (This automatically logged into my instance, you may have to provide username and password with -S and -U switches respectively)
  5. Type sp_configure, then GO (I noticed 'user connections', the config_value and run_value were set to 2 when should be 0 by default?)
  6. Run exec sp_configure 'user connections', 0. Then GO. Then reconfigure. Then GO. (I referenced this for the commad syntax. Also, the 0 value represents unlimited remote connections)
  7. Restart SQL Server (MSSQLSERVER) service
  8. Should be able to log in with SSMS and utilize all functionality.
share|improve this answer
    
Good deal, thanks for the follow up. Glad you got it working! –  squillman Feb 14 '13 at 15:36
add comment

Try stopping all of the services except for the database engine (SQL Server (MSSQLSERVER)) and see if you can connect via SSMS.

share|improve this answer
    
thank you for your answer. It, along with a few additional steps fixed my issue, I will answer my question below. –  Lane Katris Feb 14 '13 at 0:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.