Msg 18456, Level 16, State 1

Question

Msg 18456, Level 16, State 1

ram4tech

Hall of Fame

Points: 3140
More actions
March 11, 2010 at 11:56 am

#145935

Hi Gurus,
I receive Server: Msg 18456, Level 16, State 1 error when attempt to login using SQL login/pwd. I am able to login using sa credentials without any issues. I had graned sysadmin privilege to the SQL login yet the issue persists.
Can you please explain why this is happening.
Thanks,
R

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

Is user logging into local machine or from remote client? Did you login using SA credentials from remote client or locally?

EnjoY!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Have a look in the SQL error log. If login failures are logged, which I believe is the default, the failure will be listed there with a state that;s not 1. That state indicates why the login is failing. What's its value?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leksshmanan.alagappan Valued Member Points: 66 More actions · Answer 3

•The state of this 18456 error is important. However, you always receive this "Logon Error: 18456" error message that has a state set to 1 in the client application.

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error by always setting the state of the 18456 error to 1.

By default, auditing of failed logins is enabled. In this case, the true state of the 18456 error is reported in the SQL Server Error Log file

So see the error log file for the STATE of this error 18456 from the default path

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

And the entry for this login failed error in the error log would be something like this

2010-03-08 11:14:18.440 Logon Error: 18456, Severity: 14, State: 8.

2010-03-08 11:14:18.440 Logon Login failed for user 'leks'. [CLIENT: ]

I have documented all the states with its cause and resolution in this article in my website

http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456