SQL Server – Account is currently locked out

Background

We have instituted complex passwords for our sa account.  Just the other day, unfortunately I locked it out.

Here is a background to a SQL Account creation step, the manifestation of locking it out, and one way to get things back well.

CREATE LOGIN


create login [hrdb]
with password = 'mycomplexpassword2015~'
, check_policy=on
, check_expiration=off

TRY LOGIN

With bad password

Tried connecting multiple times with bad password

Text

Login failed for user ‘hrdb’. ( Microsoft SQL Server, Error: 18456)

Image

LoginFailedForUser-BadPassword

Explanation

Received opaque error message.

 

With Good password

Finally, tried with good password.

Text

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

Image

LoginFailedForUser-GoodPassword

 

Explanation

Received revealing error message.

Remediate

Syntax


alter login [login]
with check_policy=off

alter login [login]
with check_policy=on

 

Sample


alter login [hrdb]
with check_policy=on

alter login [hrdb]
with check_policy=off

Summary

To give you a clue as to whether you trying a good or password word, you can look into the error message number.

Bad password returns 18456 while Good password returns 18486.

Also, keep in mind that the account will have to have the CHECK_POLICY option enabled for this to occur.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s