Cannot cannot to SQL Server database but can connect to Server

Problem

You can connect to SQL Server 2005/2008 instance in SQL Server Management Studio (SSMS). It list all the databases but when you try to create a query or right click on a database and select properites, you see this 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)”

Solution

You might want to look at the log file created by SQL Server. They are located in

C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLLOG

Look at the ERRORLOG file (using notepad++ or just plain notepad). Somewhere in the end it might say

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

If this is the error in the log file, you might want to run this code as it is to cure the problem. Make sure to restart the server after. Note the original code was used from this site

sp_configure ‘show advanced options’, 1;

go

reconfigure

go

sp_configure ‘user connections’, 0

go

reconfigure

go

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Cannot cannot to SQL Server database but can connect to Server

  1. Jeff Dennis says:

    Somehow the “Maximum User Connections” value was changed from “0” (unlimited) to “3”. What made it particularly difficult to correct was that I had to connect to the database to actually make the change, but it wasn’t accepting new connections…

    I had to log directly on to the server from the console in vSphere, disable the network card, stop all SQL services. Then I started only the SQL server service. I started SSMS, cancelled the logon dialog, and started a SQL query. This way when I ran the query it would only consume a single connection. I ran the above commands in the query to manually modify the “user connections” value back to “0” (unlimited). I then restarted all SQL services and was able to resume normal operations on the server.

    Now I have to figure out why it changed in the first place….

    • admin says:

      Weird things happen sometime. Once I changed my SQL Password and everything was good. Find out I could not login any more after some time. Perplex what could be wrong, I tried the old password and it worked! I have never figured out how that happened.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s