I have installed SQL Server 2005 on a Windows 2003 Server box. When I log in to the Windows 2003 Server as a user other than Administrator, and attempt to login to the SQL Server, as the "sa" user, via Microsoft SQL Server Management Studio, using "SQL Server authentication" mode, I get the following message: --- MESSAGE BEGINS --- 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) --- MESSAGE ENDS --- Though I can't absolutely confirm this, a colleague of mine, who set up the Windows Server 2003 box, tells me that, when he logged in to the box as Administrator, he was able to log in to SQL Server 2005 as "sa" user. I went to the "Surface Area Configuration for Services and Connections" and the "Remote Connections", under "Database Engine", is set to "Using TCP/IP only". Also I checked "SQL Server Configuration Manager", under "SQL Server 2005 Network Configuration" -> "Protocols for BSERVER", and "Shared Memory" and "TCP/IP" are the only two "Enabled". "Named Pipes" and VIA are "Disabled". Under "SQL Native Client Configuration-->Client Protocols", "Shared Memory", "TCP/IP", and "Named Pipes" are all enabled, and in that order. "TCP/IP" has a Default Port of 1433. Any ideas as to how to resolve this issue? I'd very much appreciate any insights.
Ensure that Mixed authentication is enabled on your SQL Server 2005 instance. (From the Server Properties\Security) And then ensure that your "sa" Login is enabled. Because it's disabled by default. Open up SSMS and go to Security\Logins. Go to its Properties\Status.
Mixed authentication is enabled, as I indicated in my post when I stated that, when logged in as Administrator, we are able to login to database server as "sa" user. I checked SSMS and verifed that the login is enabled and has permission to connect to the database engine. I have since created another SQL Server login, while logged in as described in original post, and tried logging as the new user, and all goes well. I can successfully do so. The only user I can't login as is "sa".
Mixed authentication is enabled, as I indicated in my post when I stated that, when logged in as Administrator, we are able to login to database server as "sa" user. I checked SSMS and verified that the login is enabled and has permission to connect to the database engine. I have since created another SQL Server login, while logged in as described in original post, and tried logging as the new user, and all goes well. I can successfully do so. The only user I can't login as is "sa".
Last night I met this very same error on a customer's SQL Server. It's weird that I only see this message for once and then the error message changed to "Login failed for user '...'. (Microsoft SQL Server, Error: 18456) I checked the SQL Error Logs and I saw that he was using a wrong "sa" password. I highly recommend you to check your SQL Error Logs and see if your situation is the same as my customer's... SQL Server does not tell much in its Error Messages to prevent security issues. Error Logs are more informative. P.S. My customer was sure of using the correct password for his "sa" Login... But the password was not correct.
Well, lo and behold, I was not using the correct sa password! I was using the password that I was told was correct, but it wasn't. As soon as I used the correct password, everything was fine. Sorry...
