We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a regular basis. I won’t go into why other than to say we did resolve it eventually. This was a major problem that escalated rather quickly up our management chain. We needed to solve it ASAP. And because of that we needed not only a long term solution but a short term one as well. The short term solution involved creating a script that unlocked the login (if it’s currently locked) and sticking it in a job that runs every 5 minutes.
Note: This is not something you should be doing in production! This creates a major security hole.
If I could have easily made that statement blink as well as red and bold I probably would have. Do not use this in production. That said, the pieces of this script are highly useful so after much thought I decided to share. It’s not like any of it is secret after all. The script is fine, it’s just putting it in a regular job that becomes an issue.
IF LOGINPROPERTY('SQLID','islocked') = 1
BEGIN
ALTER LOGIN SQLID WITH CHECK_POLICY=OFF;
ALTER LOGIN SQLID WITH CHECK_POLICY=ON;
END
The first part of the script LOGINPROPERTY is checking the islocked flag to see if the login is currently locked. The property functions are highly useful in scripts like this so remembering they exist is a good idea. Why check? I don’t want to make any changes to the login unless I have to. Also changing the CHECK_POLICY flag to off the password history is reset (among other things). Not really a huge issue in this case but something to remember.
Next, you use ALTER LOGIN to turn the check_policy flag off and then on again. This is how you unlock the login. I should point out that this is also how you would do this through the GUI. You’d uncheck the CHECK POLICY checkbox, save, go back into the properties window and re-check it.
If you want to unlock it and are ok with changing the password at the same time you can just do this:
ALTER LOGIN [SQLID] WITH PASSWORD = '****' UNLOCK ;
But you are going to need a new password each time.