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 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.

share|improve this question
    
Edit: The error message posted above was not the one I wanted to post. The following: Login failed for user 'Master80'. Reason: Could not find a login matching the name provided. [CLIENT: Server IP Addres] –  Sean Perkins Mar 28 at 18:21
    
I was talking to my other DBA, he decided to add a Master80 account at the server level to see what would happen. Either way, my question still stands of what I can do to find what and where this is coming from. –  Sean Perkins Mar 28 at 18:23
    
My version of SQL is 10.50.4000, Standard. –  Sean Perkins Mar 28 at 19:57
    
I learned enough "ways of the force" to leverage Profiler to ascertain the offending application. I wasn't able to get XE to work or report properly. –  Sean Perkins Apr 22 at 18:32

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Apparently my version of SQL server doesn't have auditing available. I should've stated that initially, sorry for wasting your time on the good write up :( –  Sean Perkins Mar 28 at 19:56
1  
@SeanPerkins No problem, could still be helpful to others. You should tag and/or mention both the version and the edition of SQL Server, since those can often dictate what options you have. –  Aaron Bertrand Mar 28 at 19:57
1  
Perhaps an extended event could capture the application name or other useful info? On my phone, so cannot give a sample or try it out. :-( –  Max Vernon Mar 28 at 22:28
2  
@Max thanks, an XE session definitely can capture the application name - updated my answer. Cheers. –  Aaron Bertrand Mar 29 at 4:59
    
Anytime I can help you out is totally worth it! You've helped me out of more than one jam!!! –  Max Vernon Mar 29 at 19:38

Do you have Microsoft Dynamics installed?

There's a clear reference to a Master80 account in the above link:

Expand your instance of SQL Server.
Expand Security.
Double-click Logins.
Right-click Master60Sp (Master80 in Dynamics SL 2011), and then click Delete.
share|improve this answer
    
I'm just now getting back to this after being gone awhile. Yes, we do have SL installed on the Citrix servers, but we can't figure out where it is coming from specifically. I'm in the process of running XE/profiler to see if it comes up with something identifiable. –  Sean Perkins Apr 22 at 14:03

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.