?
Solved

Troubleshoot logon failure

Posted on 2014-02-11
4
Medium Priority
?
263 Views
Last Modified: 2014-02-26
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
0
Comment
Question by:Philip Pinnell
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Leon Kammer earned 1332 total points
ID: 39850961
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 668 total points
ID: 39870267
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
 
LVL 5

Assisted Solution

by:Leon Kammer
Leon Kammer earned 1332 total points
ID: 39870487
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
 
LVL 13

Author Closing Comment

by:Philip Pinnell
ID: 39888387
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question