SQL Server Security – Handling Failed Authentication Attempts

Securitysql serversql-server-2008-r2trace

I'm having a hellacious time trying to figure out what is attempting to authenticate against a particular database server, running SQL Server 2008 R2 Standard Edition. The following is the error littered in my logs:

Login failed for user 'Master80'.
Reason: Password did not match that for the login provided.
[CLIENT: Server IP Address]

The interesting thing is that this account is nowhere to be found in Active Directory or within the database server, not even specific databases. I've gone through every security folder and searched AD high and low, nothing, no remnants of this account.

As far as the "Server IP Address" part of the error, that changes between 3 or 4 CITRIX servers. So, the only thing we know for sure is that it coming from a specific handful of servers and maybe some application, but no one knows which application. Perusing the server and opening apps from the program menu or checking Add/Remove Programs netted nothing.

What is it that I can do to track this down? It was suggested to start a trace, but I'm not sure what to configure as tracing is kind of nebulous to me.

Best Answer

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:

enter image description here

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.

Related Question