SQL Server, how to log client PID of failed login attempts?

Question

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?

Answer 1

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.

Answer 2

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.