?
Solved

Troubleshoot logon failure

Posted on 2014-02-11
4
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 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