Home / server / Questions / 234803
In Process
NitroxDM
Asked: 2011-02-13 10:12:18 +0800 CST

Can not connect to SQL server with SQL Authentication. But can using Windows Authentication

  • 772

The server authentication is set to:

SQL Server and Windows Authentication mode

I can connect using Window authentication but not SQL authentication.

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

Error Number: 18456
Severity: 14
State: 1

I have reset the sa password and started and stopped the serveries to no avail.

I also created a new login. No dice.

UPDATE:

There as a process on another computer that was stuck in a loop. Once I killed the process I was able to login. I don't know why or how. But that is one only thing that I know of that changed.

sql-server sql-server-2005

2 Answers

  1. IIRC you need to enable the account as well as set the password:

    ALTER LOGIN sa ENABLE;

    (there may be a GUI equivalent too)

    • 0

  2. This is an answer to the bounty rather than the original question. I think these are two different problems.

    NitroxDM was experiencing Error Number: 18456, Severity: 14, State: 1.

    State 1 now occurs when a login is disabled - but actually, the error in the log is 18470, not 18456 - because the login is disabled, it doesn't get that far. See state 7.

    I think the process on the other machine had an incorrect password and was disabling the account.

    Steinin is experiencing Error 18456. Severity: 14 State 8. Password did not match that for the login provided.

    Probably the simplest of all: the password is incorrect (cASe sEnsiTiVitY catches a lot of folks here). Note that it will say "the login provided" even if you attempted to connect as a contained database user but forgot to specify a database, specified the wrong database, or typed the password incorrectly - unless it finds a match, SQL Server doesn't have any idea you were attempting to use a contained database user.

    Both of these descriptions came from here.

    I know you say you're able to connect with those credentials via osql. Either there's a case sensitivity issue, or SSMS has cached incorrect credentials. Try deleting or renaming

    %AppData%\Microsoft\Microsoft SQL Server\[versionnumber]\Tools\Shell\SqlStudio.bin

    and see if that helps.

    • 0