Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not sure of the change that caused this (the logs don't show anything specific) but ever since Monday afternoon I've seen the following error repeated:

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

The IP reported is the SQL Server. Since this is ongoing, is there a way I could figure out what is trying to pass this credential? If I could get an application name or anything I could probably fix the problem.

share|improve this question
2  
Perhaps using Profiler would be best option or using extended events trace if you are using SQL Server 2008 and above see this link bennettadelson.wordpress.com/2012/08/22/… –  Shanky Aug 6 at 18:17
1  
That actually got it figured out. Could you just make that the answer, maybe with a link to some doc on Profiler in case the next person looking for something similar can find this and get the help? –  Sean Long Aug 6 at 18:23

2 Answers 2

up vote 3 down vote accepted

To track login failure reason SQL Server Profiler would be a good option. One can also use extended events trace to capture login failed events and reason behind the failure.

You need to first launch SQL Server Profiler.Create a new template or you can use existing one if you have already. Please include below when creating the trace

1.Audit Login Failed (under Security Audit)
2.User Error Messages (under Errors & Warnings)
3. Errorlog (under Errors & Warnings)

Then clear the Show All Events tick box and select following columns

TextData
SPID
ClientProcessID
Hostname
LoginName
NTUserName
NTDomainName
ApplicationName
DatabaseName
Error

More details can be taken from This Link and This Link

To read more about SQL Server profiler refer to this Microsoft Online Doc

share|improve this answer
1  
I'd be careful about using Profiler explicitly for this, especially if you have databases in full recovery model with frequent log backups and no trace flag suppressing success messages from going to the error log. Create the trace using profiler, sure, but run it as a server-side trace. Or better yet, use Extended Events. –  Aaron Bertrand Aug 6 at 18:45
    
Agree Aron extended events is better option. I also mentioned about extended vents trace. Believe me I have used profiler and it works just fine apart from few cases you mentioned –  Shanky Aug 6 at 18:51
1  
My note was trying to persuade you to not recommend it in general when you have no idea what readers' scenarios are. Profiler is a very effective way to bring your server to its knees in a variety of ways and it shouldn't be suggested without some serious salt. –  Aaron Bertrand Aug 6 at 18:57

In 2008 / 2008 R2, you can set up the following session:

CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      AND state > 1 -- removes redundant state 1 event
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedLogins.xel',
    METADATAFILE = N'C:\temp\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

Then you can poll the session data periodically using the following query:

;WITH event_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'C:\temp\FailedLogins*.xel', 
   'C:\temp\FailedLogins*.xem', 
   NULL, NULL
 )
),
tabular AS
(
  SELECT 
    [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT [host],[app],[state],[message],[date/time]
  FROM tabular
  WHERE error = 18456 
  ORDER BY [date/time] DESC;

You may have to filter out some false positives here (there may be other Severity 14 errors that are not login failures).

[Shamelessly stolen from this post of mine.]

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.