0

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.

NitroxDM
  • 635
  • 1
  • 15
  • 29
  • Please post your update as an "answer" - you will then be able to select that as the "correct" answer to the question, so that it can help others who may come along in the future with the same issue. – Mark Henderson Feb 12 '11 at 20:16
  • @Mark Henderson -- I don't believe the update to be an acceptable answer. Having a stuck process on another computer doesn't explain why just the SQL authentication quit working. If someone could correlate the two that would be an answer. IMHO – NitroxDM Feb 14 '11 at 16:51

2 Answers2

0

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

ALTER LOGIN sa ENABLE;

(there may be a GUI equivalent too)

David Spillett
  • 22,754
  • 45
  • 67
  • Been there done that. ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF GO ALTER LOGIN [sa] ENABLE GO – NitroxDM Feb 12 '11 at 19:00
0

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.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • 1
    Thank you for your response. I should perhaps have asked this question separately but a quick search regarding asking the same question twice on this site dissuaded me from doing so. The original question seemed to have the exact same problems so I assumed the problem was the same or very closely related. A mod can split these questions up if they so desire. I do not believe I am inputing the password in the wrong case as I am copy-pasting it from a temporary document. I'll look into the caching as soon as I am able. – Steinin Apr 09 '15 at 05:24
  • Also, you can't copy and paste into a command line. Are you sure you're typing what you're pasting? (No confusing 0 and O bits?) And are you sure you're going to the same SQL Server instance in both SSMS and osql? – Katherine Villyard Apr 09 '15 at 12:04
  • I am attempting to connect to the database using the sql server management studio connect window. I am using the correct credentials and there is no longer any doubt about that. I have been extremely careful to write it in the dialog exactly as I wrote when I ran osql. Sqlrun also works fine. One odd thing was when I wrote the password into the SSMS password field, it took longer and longer for the star characters to appear. The last character took several seconds. The server host machine is running the F-Secure anti-virus suite. Could it be interfering with the login process? – Steinin Apr 14 '15 at 11:47
  • Finally it cannot be any SqlStudio cached credentials because the file doesn't exist as we haven't managed to log on even once using sql server authentication. – Steinin Apr 14 '15 at 11:52