Solved

Troubleshoot logon failure

Posted on 2014-02-11
4
253 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 333 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 167 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 333 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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