Login failed for user 'sa' because the account is currently locked out. The system administrator can
- by cabhilash
Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486)
SQL server has local password policies. If policy is enabled which locks down the account after X number of failed attempts then the account is automatically locked down.This error with 'sa' account is very common. sa is default administartor login available with SQL server. So there are chances that an ousider has tried to bruteforce your system. (This can cause even if a legitimate tries to access the account with wrong password.Sometimes a user would have changed the password without informing others. So the other users would try to lo)
You can unlock the account with the following options (use another admin account or connect via windows authentication)
Alter account & unlock
ALTER LOGIN sa WITH PASSWORD='password' UNLOCK
Use another account
Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. ( You can provide extra security by installing firewall or changing the default port but these measures are not always practical). As a best practice you can disable the sa account and use another account with same privileges.ALTER LOGIN sa DISABLE
You can edit the lock-ot options using gpedit.msc( in command prompt type gpedit.msc and press enter). Navigate to Account Lokout policy as shown in the figure
The Following options are available
Account lockout threshold
This security setting determines the number of failed logon attempts that causes a user account to be locked out. A locked-out account cannot be used until it is reset by an administrator or until the lockout duration for the account has expired. You can set a value between 0 and 999 failed logon attempts. If you set the value to 0, the account will never be locked out.
Failed password attempts against workstations or member servers that have been locked using either CTRL+ALT+DELETE or password-protected screen savers count as failed logon attempts.
Account lockout duration
This security setting determines the number of minutes a locked-out account remains locked out before automatically becoming unlocked. The available range is from 0 minutes through 99,999 minutes. If you set the account lockout duration to 0, the account will be locked out until an administrator explicitly unlocks it.
If an account lockout threshold is defined, the account lockout duration must be greater than or equal to the reset time.
Default: None, because this policy setting only has meaning when an Account lockout threshold is specified.
Reset account lockout counter after
This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts. The available range is 1 minute to 99,999 minutes.
If an account lockout threshold is defined, this reset time must be less than or equal to the Account lockout duration.
Default: None, because this policy setting only has meaning when an Account lockout threshold is specified.When creating SQL user you can set CHECK_POLICY=on which will enforce the windows password policy on the account. The following policies will be applied
Define the Enforce password history policy setting so that several previous passwords are remembered. With this policy setting, users cannot use the same password when their password expires.
Define the Maximum password age policy setting so that passwords expire as often as necessary for your environment, typically, every 30 to 90 days. With this policy setting, if an attacker cracks a password, the attacker only has access to the network until the password expires.
Define the Minimum password age policy setting so that passwords cannot be changed until they are more than a certain number of days old. This policy setting works in combination with the Enforce password historypolicy setting. If a minimum password age is defined, users cannot repeatedly change their passwords to get around the Enforce password history policy setting and then use their original password. Users must wait the specified number of days to change their passwords.
Define a Minimum password length policy setting so that passwords must consist of at least a specified number of characters. Long passwords--seven or more characters--are usually stronger than short ones. With this policy setting, users cannot use blank passwords, and they have to create passwords that are a certain number of characters long.
Enable the Password must meet complexity requirements policy setting. This policy setting checks all new passwords to ensure that they meet basic strong password requirements.
Password must meet the following complexity requirement, when they are changed or created:
Not contain the user's entire Account Name or entire Full Name. The Account Name and Full Name are parsed for delimiters: commas, periods, dashes or hyphens, underscores, spaces, pound signs, and tabs. If any of these delimiters are found, the Account Name or Full Name are split and all sections are verified not to be included in the password. There is no check for any character or any three characters in succession.
Contain characters from three of the following five categories:
English uppercase characters (A through Z)
English lowercase characters (a through z)
Base 10 digits (0 through 9)
Non-alphabetic characters (for example, !, $, #, %)
A catch-all category of any Unicode character that does not fall under the previous four categories. This fifth category can be regionally specific.
