What is causing Logon errors in the Current Log??? RRS feed

  • Question

  • Getting this error over and over in the SQL Server Logs:

    Log        SQL Server (Current - 3/28/2017 4:50:00 PM)
    Source        Logon
    Message:    Error: 18456, Severity: 14, State: 8.

    Date        3/28/2017 4:48:30 PM
    Log        SQL Server (Current - 3/28/2017 4:50:00 PM)
    Source        Logon
    Login failed for user 'username'. Reason: Password did not match that for the login provided. [CLIENT: 10.20.100.XXX]

    How can I tell where the login error is originating from?  It gives me an IP of the application server, but how can narrow down the process causing this logon failure?

    Wednesday, March 29, 2017 12:07 AM

All replies

  • Hi, the default trace may be able to help i.e. you can get the host, PID on the host and application name

    SELECT TextData,HostName,ClientProcessID,ApplicationName
    FROM master.sys.fn_trace_gettable(
    ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) + 'log.trc'
    FROM  sys.traces
    WHERE is_default = 1
    ), DEFAULT) a
    WHERE TextData LIKE 'Login%';

    Wednesday, March 29, 2017 12:19 AM
  • Check task scheduler to see if there is a scheduled task. Check odbc administrator for user or system dsn's which might contain a reference to this server.

    Check to see if there is a web server on this machine which might be making connections to this SQL Server.

    Finally you might want to run process monitor from the command line to log to a file for later analysis. Use the command line switches:

    C:\Users\hilar\Downloads\ProcessMonitor>procmon /backingfile c:\temp\out.pml /quiet /minimized

    use this to cancel it

    C:\Users\hilar\Downloads\ProcessMonitor>procmon /terminate

    Wednesday, March 29, 2017 12:22 AM
  • Thank you!  Thanks for those tips too, will save them for reference.
    • Edited by irvineuser Wednesday, March 29, 2017 12:39 AM
    Wednesday, March 29, 2017 12:29 AM
  • Thanks!  I ran it and I see the same ClientProcessID listed for all these errors.  Would the ClientProcessID be the Windows Process ID (I see one by that number on our application server for a running service under Windows Task Manager!)?
    • Edited by irvineuser Wednesday, March 29, 2017 12:32 AM
    Wednesday, March 29, 2017 12:30 AM
  • Yes that's correct, it's the Process ID on the source server.
    Wednesday, March 29, 2017 12:34 AM
  • Amazing, thanks for helping me get to the root of this so quickly!! :-)  Now for more drilling on that Process.
    Wednesday, March 29, 2017 12:38 AM
  • Sorry, but do either of you (or anyone else out there) know of a way to tie those trace entries to a SPID so it might indicate what SQL statement it is attempting to run when it fails?  Thanks.
    Wednesday, March 29, 2017 12:56 AM
  • Unfortunately, you will not be able to get the statement as the login has to succeed first before the statement is run. The SPID is also shown in the default trace (append SPID to previous query) but this will likely add little value.

    Your error is indicating a password issue for 'username', are you able to verify/update the password for this user to resolve?

    Wednesday, March 29, 2017 1:10 AM
  • Thanks, KevinNicholas.  Yes, I have the proper credentials verified, but it's possible it's mis-coded differently in some script (we have several via that service).  Will open up the code to validate the credentials entered for each instance.  Thanks again.
    Wednesday, March 29, 2017 1:14 AM
  • Can you check that your server is enabled or windows and sql authentication? It looks like you are running a windows workgroup and your password are different, or you are running sql authentication and your server is enabled only for windows authentication or you have the wrong password.

    The logon attempt has failed, there is no spid as Kevin points out.

    Wednesday, March 29, 2017 1:22 AM