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.

I have SQL 2008 server installed on the local machine with the instance name "SQL2008". I'm trying to connect to it using Management Studio. I can connect with the sa authentication but am unable to connect with the windows authentication.

More details:

  • The server is installed on the local machine.
  • I'm using <local machine name>\SQL2008 to connect
  • The error message is "Cannot connect to <local machine name>\SQL2008" and "Login Failed for user '<domain>\administrator'. (Error: 18456)
  • I'm able to connect with sa authentication
  • I'm NOT able to connect with windows authentication
  • I'm sure the password for administrator is correct. I have logged on to the machine as administrator with the same credentials

Could someone please give any suggestions? I'm not much experienced with SQL server and this is very urgent. Thanks.

share|improve this question
    
Open the SQL Server Error log and you will have entry regarding this Login failure along with the reason for it. –  Pradeep Adiga Dec 14 '10 at 12:40
    
Thanks. I have added the log to Nick's answer below. –  Mugen Dec 14 '10 at 12:52

3 Answers 3

up vote 0 down vote accepted

What is the 'state' refer to this blog entry for an explaination of what im refering too. Should be able to see this either in the windows event log or the ERRORLOG file for sql server.

share|improve this answer
    
The ERRORLOG states: 2010-12-14 18:07:05.31 Logon Error: 18456, Severity: 14, State: 11. 2010-12-14 18:07:05.31 Logon Login failed for user 'EPOMFE\Administrator'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 172.16.x.x] –  Mugen Dec 14 '10 at 12:42
    
I googled on this error and found the following solution to my problem: sqlservercentral.com/Forums/Topic644009-6-3.aspx#bm946965 Basically the SQL server was installed as workgroup and now brought to the domain. This problem occurs for SQL 2008 only. Thanks Nick. –  Mugen Dec 14 '10 at 12:59

Can't access SQL Server 2008 with anything but 'Windows NT Integrated Security'

share|improve this answer
    
i believe this is the opposite issue. i.e. intergrated doesnt work, but not sql auth does. –  Nick Kavadias Dec 14 '10 at 12:25
    
Yeah I guess this is the oppposite issue. Thanks for the information though. :) –  Mugen Dec 14 '10 at 12:33

Have you added your windows users as SQL users in SSMS? To do this you would log into SSMS via SA (in your case) and expand the security folder, then go to logins. Right-click to add a nrw login and within that popup screen you will see where you can link it with your windows users and give the user the appropriete level of SQL permissions.

This should do the trick for you.

Nate

share|improve this answer
    
Thanks for this answer Nate. By the time I got this I had already figured this out with Nick's answer. –  Mugen Dec 15 '10 at 12:15

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.