September 22, 2014 at 7:54 am
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?
September 23, 2014 at 12:22 pm
Run a trace and find the process id, login to your server and find the process that is using that process id.
September 24, 2014 at 1:06 pm
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
September 24, 2014 at 1:37 pm
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