Troubleshoot logon failure

SQL 2005

I have recently noticed in the SQL logs a message

"Login failed for user 'sa'. [CLIENT 10.0.2.85]"

This has been happening a lot over quite a long period.

I know what the machine is but I cannot work out what application is causing this. I cannot find any error logs that show a problem. The timing is a bit random.

How can I go about identifying the 'culprit' ?

TIA
LVL 13
Atdhe NuhiuAsked:
Who is Participating?
 
Leon KammerConnect With a Mentor Commented:
Hi,

You should not really be allowing sa to connect into the instance, and generally it is best to disable the login, but that aside, I would for a start by going into the event viewer on the machine 10.0.2.85 and look for a SQL connection error (error 18456)

Open Event Viewer and right click event viewer, and create a custom view, This will open the Filter Current Log dialog box which will allow you to specify logs, time period and keywords.

Cheers

Leon
0
 
Jim P.Connect With a Mentor Commented:
Leon,

The sa account should generally never be disabled because SQL still uses the account itself internally. Turning of mixed mode authentication is different.

But if there is an app using sa to connect it needs to be changed by the developer.

If the IP is 10.0.2.85 that means it is an internal network machine, so should be fairly easy to track down and see what is installed on it.
0
 
Leon KammerConnect With a Mentor Commented:
If you are still having issues attemtping to identify which application is attempting to connect to the SQL server, try using a traffic logger such as Tcplogview on the machine 10.0.2.85 http://www.nirsoft.net/utils/tcp_log_view.html

Cheers

Leon
0
 
Atdhe NuhiuAuthor Commented:
Thanks for your suggestions. I have been sidetracked with other things.

I think I will use the profiler to identify what eaxactly is failing to log on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.