SQL Server Security – How to Log Client PID of Failed Login Attempts

Securitysql server

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?

Best Answer

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.

Related Solutions

SQL Server Security – Handling Failed Authentication Attempts

My initial thought was a logon trigger but, sadly, those only fire after a successful login.

The documentation for the Audit Login Failed Event Class says that failed login audits will include the application name, but I haven't observed that so far (I only tested failed logins from changing connections within Management Studio, so maybe it is exposed in other scenarios). Anyway, create this audit and audit specification:

USE master;
GO

CREATE SERVER AUDIT failed_logins                                         
  TO FILE (FILEPATH = 'C:\temp')
GO

ALTER SERVER AUDIT failed_logins 
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION failed_logins_spec
  FOR SERVER AUDIT failed_logins
  ADD (FAILED_LOGIN_GROUP)
  WITH (STATE=ON);
GO

Now you should be able to right-click the audit (in Object Explorer under Security > Audits) and choose View Audit Logs. There may be more information there for you, that might help you narrow this down better, but I am not 100% certain. The application trying to connect would have to pass a valid application name, Citrix can't be blocking any of that information from passing through (or changing it on the way), etc.

Note that this may be a no-op depending on your version and edition, too. If so, your next option may be Extended Events, as Max pointed out below.

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 -- login failed - 2012+ only!
      AND state > 1 -- seems to always be a 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

You can write really ugly XQuery to parse the results, or get Jonathan Kehayias' SSMS add-in.

In 2012 you can just open a Watch Live Data window by right-clicking the session in Object Explorer: Management > Extended Events > Sessions and wait for it to happen again. You might get noise from other "real" failed logins, password typos, other severity 14 errors, etc. In 2012+ you will be able to bind error_number instead, but it is not valid on 2008. Anyway, here is what I saw in 2014 with the 18456 error filter:

And while I have no interest in going the extra length to craft the XQuery you need to see query results in a nice tabular format, I did verify that in 2008 the above session does record the application name in the .xel file target by opening it in Notepad (it ain't pretty).

If that doesn't work (e.g. the app name does not get populated for some reason), next would be some kind of network/packet sniffer, like Wireshark. You could also:

Look at Control Panel > Administrative Tools > Services to see if there are any services running that are set to start up using that account.
Look at the Event Logs on both the SQL Server machine and an offending Citrix machine for any other clues around the time of a login failure.
Change the hosts file on that machine to override the SQL Server DNS name to point elsewhere (e.g. to an IP that is unreachable), and see if anything breaks in a different way if the app or service can't connect to SQL Server at all. Who knows, it may actually log something or pop up a dialog in that case. This will only work, of course, if the app currently reaches the SQL Server by name resolution and not by IP - you may need to try both the Windows name and the FQDN. This should work to stop the login attempts at least, even if it doesn't yield any more clues about the source; if so, then at the very least this could be a permanent way to stop filling the SQL Server's logs with failed login attempts. If other apps on that machine do need to connect to SQL Server, you could have them connect by IP instead, or (probably more future-proof) you could set up a different alias and change their connection strings to use the new name.

I also have a pretty elaborate blog post detailing what the state means for all the 18456 errors.

SQL Server Authentication – Fixing Login Failed Issues

Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message

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

What is output of this query.

select serverproperty('IsIntegratedSecurityOnly')

It seems like authentication mode is Windows only. If above query returns 1. It means authentication mode is windows go to server . Right click on SQL Server select property and then click on security. Change authentication it to mixed mode by selecting radio button SQL Server and windows authentication mode and restart SQL Server service again.