none
Cannot connect to SQL Server 2005; Error: 18456

    Question

  • I get the message below when trying to connect SQL Server 2005 in SSMS (also unable to connect databases from other programs). It is happening with Database Engine but not with Analyis Services. Everything has been working fine for several years, the problem just started this afternoon. BTW, it uses Windows Authentication. I've tried restart SQL Server and I tried rebooting the computer all to no avail. Can anyone help?

     

    Cannot connect to <ComputerName>\<ServerInstanceName>.

    Login failed for user '<ComputerName>\<UserName>'. (Microsoft SQL Server, Error: 18456)

    Tuesday, October 21, 2008 6:00 PM

Answers

  • I resolved it! I opened SQL Server Surface Area Configuration, clicked on Add New Administrator. For reasons unknown, I was not listed as the administrator (even tho I had been for several years). I knew some setting would have to have been changed recently; the trick was knowing which one. After finishing this procedure, I was again able to sucessfully access the the Database Engine.

     

    Hope this info helps others in this situation.

    Thursday, October 23, 2008 12:40 PM

All replies

  • Can you look at the server's error log, and post the results of that login attempt here?  The "login failed for user" message is deliberately generic for security purposes.

    Is the login issue occuring for one user only, or for all users?

     

    Tuesday, October 21, 2008 9:58 PM
  • Tuesday, October 21, 2008 10:24 PM
  •  

    Can you share the complete error message?... which will be like this

    Error: 18456, Severity: 14, State: 8

     

    according to this state number you are getting you can troubleshoot the problem. See the following link from the books online.

    Troubleshooting: Login Failed for User 'x'

    Wednesday, October 22, 2008 7:59 AM
  • The links offered by others did not provide any useful information. The error message I listed is complete except for repetitive information from the dialogbox title, computer name and user name. No information has been presented regarding State. The computer is my personal laptop; I am the only user. As I mentioned, Analysis Services logs in without trouble. All had been working fine for several years; so this is something new. Something changed yesterday - perhaps a setting somewhere.

     

    This was in the log just now when I tried to login using SSMS:

     

    - System

      - Provider

       [ Name]  MSSQL$SQLSERVER2005
     
      - EventID 18456

       [ Qualifiers]  49152
     
       Level 0
     
       Task 4
     
       Keywords 0x90000000000000
     
      - TimeCreated

       [ SystemTime]  2008-10-22T12:15:37.000Z
     
       EventRecordID 18832
     
       Channel Application 
     

     

    Wednesday, October 22, 2008 12:21 PM
  • So far, no one seems to be able to help. At this point it seems my only choice would be to remove SQL Server 2005, then reinstall it. Of course, there's no guarantee that this would even help. While not my desired choice, I've been four days without the ability to access my databases and I need to do something soon.

     

    2008-10-23 09:45:49.34 Logon       Error: 18456, Severity: 14, State: 11

     

    I know State 11 means 'Login is valid, but server access failed' but is not exactly helpful.

     

    Perhaps someone in another forum would be able to help. Can anybody recommend a better forum to ask this question?

     

    Thursday, October 23, 2008 11:34 AM
  • I resolved it! I opened SQL Server Surface Area Configuration, clicked on Add New Administrator. For reasons unknown, I was not listed as the administrator (even tho I had been for several years). I knew some setting would have to have been changed recently; the trick was knowing which one. After finishing this procedure, I was again able to sucessfully access the the Database Engine.

     

    Hope this info helps others in this situation.

    Thursday, October 23, 2008 12:40 PM
  • rlarzen,

     

    I hope you can help me, I am having the same problem out of nowhere, and I tried your solution, but it won't let me finish creating a new sysadmin under my credentials.

     

    Can you explain how you were able to do this.

     

    Thanks

    Monday, November 03, 2008 2:02 PM
  • All I did was what I wrote in my message above: Opened SQL Server Surface Area Configuration; clicked on Add New Administrator; that opened a dialog box SQL Server 2005 User Provisioning Tool for Vista which shows Available Privileges list and Privileges that will be granted to mycomputer\myusername; I selected the SQL Server 2005 instance then clicked the > key; then OK; closed SQL Server Surface Area Configuration; restarted SQL Server and all was well with the world again.
    Wednesday, November 19, 2008 9:07 PM
  • Thanks for the response.

     

    I actually fixed my own problem a little while back.

     

    Somehow the Builtin\Administrators group was removed from the Security\Logins.

     

    Luckily we had a sql admin login and then I restored the group to the Security\Logins.

     

    Which allowed me to login using Windows Authentication with full admin privileges.

    Thursday, November 20, 2008 2:05 PM