1

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.

Improve this question
1
  • Open the SQL Server Error log and you will have entry regarding this Login failure along with the reason for it.
    – Nomad
    Dec 14, 2010 at 12:40

3 Answers 3

Reset to default
0

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.

Improve this answer
2
  • 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, 2010 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, 2010 at 12:59
0

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

Improve this answer
2
  • i believe this is the opposite issue. i.e. intergrated doesnt work, but not sql auth does.
    – Nick Kavadias
    Dec 14, 2010 at 12:25
  • Yeah I guess this is the oppposite issue. Thanks for the information though. :)
    – Mugen
    Dec 14, 2010 at 12:33
0

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

Improve this answer
1
  • Thanks for this answer Nate. By the time I got this I had already figured this out with Nick's answer.
    – Mugen
    Dec 15, 2010 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.