Frequent login attempts from blank user

  • For some time now my logs have been filling up with the following error.

    Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

    The IP it is coming from is our terminal server which hosts a number of applications that connect to the SQL server as well as a few ODBC connections. None of these programs have had issues connecting, so I have no idea what this connection is coming from. I am having a hard time finding methods to track this down. What options are there to figure out what program is making this connection attempt so I can fix the problem?

  • Run a trace and find the process id, login to your server and find the process that is using that process id.

  • Don't need to run a trace, simply use the default one (if enabled). Then you can get ApplicationName and ProcessID.

    Use this query:

    DECLARE @filename VARCHAR(500)

    SELECT @filename = SUBSTRING(path, 0,LEN(path)

    - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'

    FROM sys.traces

    WHERE is_default = 1 ;

    SELECT

    te.Name AS EventName

    ,NTDomainName

    ,NTUserName

    ,LoginName

    ,ApplicationName

    ,HostName

    ,ClientProcessID AS ProcessID

    ,DatabaseName

    ,StartTime

    ,TextData

    ,Error

    ,SPID

    ,SessionLoginName

    FROM fn_trace_gettable(@fileName, DEFAULT) gt

    INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id

    WHERE EventClass IN(20) -- Audit Login Failed

    ORDER BY StartTime DESC


    Alex Suprun

  • Alexander Suprun (9/24/2014)


    Don't need to run a trace, simply use the default one (if enabled). Then you can get ApplicationName and ProcessID.

    Use this query:

    DECLARE @filename VARCHAR(500)

    SELECT @filename = SUBSTRING(path, 0,LEN(path)

    - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'

    FROM sys.traces

    WHERE is_default = 1 ;

    SELECT

    te.Name AS EventName

    ,NTDomainName

    ,NTUserName

    ,LoginName

    ,ApplicationName

    ,HostName

    ,ClientProcessID AS ProcessID

    ,DatabaseName

    ,StartTime

    ,TextData

    ,Error

    ,SPID

    ,SessionLoginName

    FROM fn_trace_gettable(@fileName, DEFAULT) gt

    INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id

    WHERE EventClass IN(20) -- Audit Login Failed

    ORDER BY StartTime DESC

    Perfect! Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply