3

Last night all of our systems failed and connections to the database kept dropping. We watched the log today and found out that this was the message:

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486)

Of course it can be solved in a minute of Googling around, via some tricks and scripts.

However, the more we search the less we find out about the reason behind this. Why it has happened in the first place at all? What possible reasons caused it, and might cause it again? Was it because of an attack? We have no clue, and any help is appreciated.

Update: in this post it's been argued that a policy check can make this happen. We haven't set a policy so far, and we do not even know where are the policies.

Update2: We realized that some of the threads in some applications kept working, even though other threads and other applications were encountering sa is locked message. Could it be that connection pooling doesn't require re-authentication? How is that possible?

  • 1
    sa should always be disabled. It's a well kown login and common attack vector. Moreover, no application should log in as sa. Sysadmin logins are for administrative tasks, not for applications. – spaghettidba Sep 19 '16 at 7:47
  • You can add your server to DBHistory.com monitoring and know exactly who and when disabled sa. – Remus Rusanu Sep 19 '16 at 10:42
  • 2
    Have you investigated the Sql Error Logs for failed login attempts to 'sa'. Perhaps the client IP address in the log entry would give you a clue as to where the failures are coming from? Perhaps enabling auditing for failed logins will also help - sqlshack.com/… – Scott Hodgin Sep 19 '16 at 11:11
2

When you look at the General tab in the Login Properties of the sa account, you will notice different settings. One of them being Enforce password policy.

I discussed this setting in my answer to the question How to lock a sql login after N unsuccessful login attempts.

Possible reasons for change in lockout behaviour

If you previously didn't have an Active Directory policy for locking out accounts, then ...

  1. ... the user's inability to use the correct password had no adverse effect on the sa account (No locking of sa account)
  2. ... the hacker was able to enter multiple passwords for the sa SQL Login until he had access. (No locking, but hacker in system)
  3. ... it didn't matter if any script, program, user, system used a wrong password for the sa account.

If you changed the Active Directory policy recently to lock accounts after n wrong password attempts or if you reduced the number of wrong password attempt to cause a lockout, then you might be encountering a locked sa account due to ...

  1. ... the user using the sa account is unable to enter the correct password after n attempts, which results in a lock out.
  2. ... the hacker is unable to gain access again, which results in a lockout.
  3. ... somebody is just having fun locking out the sa account.

Recommended actions

  1. Change the password for the sa account to ensure nobody gains access to the server via this account.
  2. Ensure that the option Failed logins only is enabled at server level in the in the Server properties - xyz | Security tab | Login auditing section . (Better: If you have enough space for your ERRORLOG and are cycling the ERRORLOG at a regular interval, set it to Both failed and successful logins.)
    Server properties | Security tab | Login auditing

  3. Check the SQL Server ERRORLOG file for login issues and track down who is behind the IP address in the error message.

    2017-07-21 09:58:12.48 Logon       Error: 18456, Severity: 14, State: 8.
    2017-07-21 09:58:12.48 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]
    

    this example was from the same host, hence no IP address

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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