2

I'm trying to figure out which process is trying to connect to my SQL Server instance with a wrong password. The log file only contains the IP address from where the connection is initiated. Example:

Date 4/05/2021 9:43:34 AM Log SQL Server (Current - 5/05/2021 10:11:00 AM)

Source Logon

Message Login failed for user 'SA'. Reason: Password did not match that for the login provided. [CLIENT: 10.120.1.99]

It does not log which PID from the client machine made the attempt.
I know that I can use profiler to find out which PID from which machine is responsible for this. But I do not want to keep a profiler running for this. (especially if this happens rarely, in which case I'll need to keep the profiler running for days before I can catch such an attempt).

Is there a way to log the PID as well as the IP for such failed logons?

5
  • I do have access to the client machine. I just thought whether this is logged there is the program's choice, so chance is not good. Also it seems very strange to me, that SQL server does not log such important information. I'm hoping this can be configured on server side. Commented May 5, 2021 at 0:51
  • Can you clarify what you mean by PID? Did you in fact mean SPID? By SPID we typically mean the session ID in SQL Server. By PID we typically mean the process ID in the operating system. Commented May 5, 2021 at 8:59
  • Was referring to process ID on client machine, I was hoping to use that (also client_app_name as mentioned by Ronaldo in his answer) to identify which program/process made the attempts. Commented May 5, 2021 at 23:47
  • I don't think that the client PID is available to SQL Server. Did you see it in Profiler? Can you say the name of the event and column? Commented May 6, 2021 at 19:34
  • Hello, SparedWhisle. Did you have any problems running the methods I provided as the answer?
    – Ronaldo
    Commented May 10, 2021 at 19:30

2 Answers 2

6

Default Server Trace

Try to get more info from the default server trace, as Eitan Blumin's article Finding the Details Missing from the SQL Server Failed Logins Audit shows using this query:

SELECT  trc.*
FROM fn_trace_getinfo(default) AS inf
CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc
WHERE inf.property = 2 AND inf.value IS NOT NULL
AND trc.EventClass= 20 
ORDER BY trc.StartTime DESC

XE Session

Alternatively, Dave Bland's article Find Failed Logins Using Extended Events has an example of how you can achieve it using an XE 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 [error_number]=(18456) AND [state]>(1)))  
ADD TARGET package0.event_file(SET filename=N'C:\temp\FailedLogins.xel')  
GO

Don't forget to create the folder listed on the path (or change it). With that XE Session you'll be able to capture the client_app_name which is what you're actually looking for instead of just the PID.

SQL Server Audit

You could also configure a SQL Server Audit, but the doc says:

SQL Server audit uses Extended Events to help create an audit.

Therefore, using an Audit would cost about the same as creating the XE Session.

1

The answer from Ronaldo above is very good, but I would add that you may need to add the field client_pid to the error_reported event. In my case the client_hostname was returning ".Net SqlClient Data Provider" which did not lead me to the culprit. The PID could be matched in the Details tab of Task Manager to point directly to a mis-configured Service that was making the failed login attempts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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