March 1, 2012 at 7:08 am
I was getting a number of errors in our SQL Server Error log (2008 R2) stating the following:
Login failed for user 'appsensesql'. Reason: Password did not match that for the login provided.
[CLIENT: 136.160.130.111]
Error: 18456, Severity: 14, State: 8.
I talked to the user at this I.P. address and they informed me that they had problems with that account (appsensesql) so they had a new sql account created "appsense" on our SQL Server DBMS (while I was off). They are no longer using the "appsensesql" on their workstation.
I then disabled the account on the SQL Server DBMS. I then got the following in the SQL Error Log:
Login failed for user 'appsensesql'. Reason: An error occurred while evaluating the password. [CLIENT: 136.160.130.111]
Error: 18456, Severity: 14, State: 7.
So I deleted the account from the DBMS. I now get:
Login failed for user 'appsensesql'. Reason: Could not find a login matching the name provided. [CLIENT: 136.160.130.111]
Error: 18456, Severity: 14, State: 5.
The user assures me that there are no services using the old account "appensesql". I turned on SQL Profiler and see the new account "appsense" logging in, but no information regarding the "appsensesql" account failing attempts.
Is there another method I can use to track down exactly what is causing these errors to occur?
Thank you in advance.
Charlie
March 1, 2012 at 7:40 am
if you create a blank trace using the blank template and just enable the following event
Security Audit --> Audit Login Failed, you should eventually get a hit.
as its a sql account, it would have to be configured in the app so will be a bit hard to track down unless you can get access to the appsence configuration and do a find and replace for the old login.
i had an issue with error 18456 last week and managed to track it down with just this one event selected
March 1, 2012 at 7:44 am
Use profiler and trace for the Audit Login Failed event, get the hostname and the application name and you should be able to see what machine and what application is using that login.
The user that says they are not using it could be mistaken, it could perhaps have been used in a couple of places and they've only changed 1.
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
March 1, 2012 at 7:58 am
Hi Anthony,
Thank you for the information. I ran the profiler earlier with Standard Template and just removed all options except for auditing successful and failed logins. I could see the new account "appsense" logging in successfully.... but nothing for the "appsensesql" account (successful or failure). I am following your method now... the profiler has been running for about 10 minutes and no failures so far. Thanks again for replying.
Charlie
March 1, 2012 at 8:01 am
Gail,
Thank you for the reply. I tried running the profiler with the standard template and removing everything except the login infomation. I saw the new account logging in successfully.... but nothing for the "appsensesql" account... it is still filling the SQL Error Log. Thank you again for the information.
Charlie
March 1, 2012 at 8:07 am
No, you don't want the standard template, that's only got Audit Login and Audit Logout. I said trace the Audit Login Failure event.
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
March 1, 2012 at 8:12 am
Hi Gail,
I actually created a new Profiler Trace per your instructions after your first post. Still no failures reported.
Charlie
March 1, 2012 at 8:29 am
The Audit Login Failure event?
Sure you're tracing the right instance? If there are login failures in the log, that profiler event will be fires (providing there are no filters defined on the trace)
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
March 1, 2012 at 8:38 am
Hi Gail,
Yep... I was tracing the correct instance. I checked the SQL Error Log before I received your last message. I noticed the messages had stopped. I contacted the user and they had corrected the issue on their workstation. Once again... thank you very much for taking your time to respond to my post... I really do appreciate the support!
Charlie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply