SQL Server – Reasons Why ‘sa’ Account Gets Locked

loginssql server

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?

Best Answer

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

... the user's inability to use the correct password had no adverse effect on the sa account (No locking of sa account)
... the hacker was able to enter multiple passwords for the sa SQL Login until he had access. (No locking, but hacker in system)
... 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 ...

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

Recommended actions

Change the password for the sa account to ensure nobody gains access to the server via this account.
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.)

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

Related Solutions

Sql-server – happening with locks in the database

When I run sp_lock, I realized that two locks in my table: PAG and KEY. Searching more about KEY LOCK, I created an index for the column on the where clause, and this solve my problem.

EDIT: When I removed the where clause (without index) , it worked fine too.

Sql-server – “A severe error occurred” – Login Failed (but only occasionally)

Just in case this helps, I'm posting some code that might help you work out what account that sid represents:

USE tempdb;
GO
-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, 
            REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END
GO

SELECT dbo.fn_SIDToString(sp.sid), sp.*
FROM sys.server_principals sp
WHERE dbo.fn_SIDToString(sp.sid) = 'S-1-9-3-3788657366-1166700905-846186909-3339902698';

Thanks goes to @James for pointing out that the sid might be in master.sys.server_principals and to @AaronBertrand for pointing me to a great piece of code that will translate SQL Server binary sids to the Windows format.

N.B.: I put this function in tempdb to avoid creating it in a permanent place. You may want to put it somewhere else for future use, since tempdb gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.