Sql – MS SQL Server 2012 – Unable to access Instance on localhost

sqlsql-server-2012

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

Best Answer

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