Where is Error 18456 coming from?

Question

Where is Error 18456 coming from?

uk00121

SSChasing Mays

Points: 640
More actions
November 30, 2015 at 2:14 pm

#399405

My SQL log is flooded with the following entries at least one per minute. Running Enterprise SQL 2012 SP1, clustered.
Login failed for user 'foo'. Reason: Password did not match that for the login provided. [CLIENT: 123.45.67.89]
Error: 18456, Severity: 14, State: 8.
The IP is the active node of the cluster (not the SQL resource).
The user id is being used extensively and successfully from multiple application servers, so cannot be easily changed/dropped/reset.
All the research I've been able to do just tells me that a connection is attempting to use the wrong password. That is clear. What is not clear is WHERE is that connection coming from so I can fix/disable it.
No jobs are failing. No packages are failing. Nothing in the application is failing. The only entry in SQL or Windows log is the one above.....login fails with bad password. This has been going on for months and makes searching the logs for legitimate errors difficult.
Can anyone tell me how to track down the source of the connection? Is the IP a red herring and this could be coming from anywhere in the organization (like a defunct report server)?

Viewing 9 posts - 1 through 8 (of 8 total)

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

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 1

Check the username and the password you are entering is correct or check the Windows Authentication is enabled. To fix this issue you can take the help of this link: http://www.sqlserverlogexplorer.com/fix-microsoft-sql-server-login-failed-error-18456/[/url]

uk00121 SSChasing Mays Points: 640 More actions · Answer 2

Thanks but that doesn't answer my question. The account is being successfully used by the application and I can use it to connect directly to SSMS. I know the account is good.

What I need it to identify WHERE the wrong password is being sent from, so I can fix it there.

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

All that can be determined from the error is that it's some app on the machine 123.45.67.89. If that's the active node of the cluster, then there is something running on the active node of the cluster that's trying to connect with the wrong password. It will not be from any other machine on the network.

Try setting up an server-side trace with the Audit Login Failed event. that should give you more information about the name the app is sending to SQL, hopefully will help you nail down the cause

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

uk00121 SSChasing Mays Points: 640 More actions · Answer 4

Ran the trace and even more confused now.

EventClass - Audit Login Failed

TextData - Login failed for user 'foo'. Reason: Password did not match that for the login provided. [CLIENT: 123.45.67.89]

ApplicationName - Microsoft SQL Server

LoginName - foo

ClientProcessID - 121124

SPID - 270

StartTime - 2015-12-01 14:08:00.850

DatabaseID - 1

DatabaseName - master

Error - 18456

EventSequence - 22903

EventSubClass - 1 - Nonpooled

HostName - MyBox

RequestID - 0

Servername - MyBox

SessionLoginName - foo

State - 8

Success - 0

Type - 1 - Non-DAC

* MyBox = cluster network name of sql instance (default), IP = active node of the cluster

According to this SQL is trying to log into itself (!?!?!?!) and failing with an account that it being used by multiple application servers. I tried tracking the spid but by the time it shows as a failed login the spid it either cleared or reassigned to another process.

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

Could be a maintenance plan, a linked server (loopback linked server perhaps)

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

uk00121 SSChasing Mays Points: 640 More actions · Answer 6

No linked server (or other server objects)

No maintenance plans

Only handful of jobs that always run successful

No local windows tasks I can find

Entries are hitting the log anywhere from 20-120 seconds, but not a consistent pattern to think it's a heartbeat/retry.

If something keeps failing to connect, you'd think whatever was trying would report a failure as well. All the logs provide is SQL saying "someone failed to get into SQL" and the trace shows "SQL failed to get into SQL"......BOGUS!

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

If the trace shows it's coming from SQL, then it's coming from SQL. It's not bogus, the trace is telling you what application name the client is passing. Is there another instance of SQL on that server, maybe express or a standalone instance that it could be coming from?

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

uk00121 SSChasing Mays Points: 640 More actions · Answer 8

Sorry....meant "bogus" as in "expletive of frustration"

This is part of a cluster with two other instances. The other two instances are not on the node that matches the IP and neither have linked servers.