October 17, 2012 at 6:22 am
Yesterday, I have faced an incident that users were getting login locked out error in their applications.
I wanted to check the properties of this login but was getting "Unable to edit properties" message. I then tried to unlock the login using t-sql, but it didn't work. Since, this SQL instance was running on cluster nodes, I failed over the SQL instance to other node. Login gets unlocked automatically thereafter.
I am wondering what could be the reason for this. Can anyone help me on this?
Regards
sqldba4u
October 17, 2012 at 6:30 am
Check is there policies enforced?
October 17, 2012 at 6:46 am
Yes password policy is inforced. Is this the cause? If yes, how?
October 17, 2012 at 6:56 am
This will enforced the login of the Windows password policies of the computer on which SQL Server is running.
October 17, 2012 at 7:04 am
Ok. But this is SQL login and how come Windows password policy will come into effect here.
Can you please help me understand the scenario in detail?
Thanks
October 17, 2012 at 7:12 am
October 17, 2012 at 7:20 am
I have gone thru the blog. Mine is Win 2003. It says login can be locked out if inputting wrong password continuously for certain times. Also, CHECK_EXPIRATION is off for this login. I wanted to know why it got locked and only after failover the login got unlocked automatically without any manual work.
Thanks
October 18, 2012 at 6:44 am
The ENFORCE PASSWORD POLICY option is checked on the userid.
October 18, 2012 at 8:04 am
So that's the only reason for the locking of the login?
October 18, 2012 at 8:07 am
sqldba4u (10/18/2012)
So that's the only reason for the locking of the login?
If you have the 'Enforce Password Policy' checked and that user then puts the password in incorrectly more times than your Network password policy it will lock out the id.
October 18, 2012 at 8:35 am
Markus (10/18/2012)
sqldba4u (10/18/2012)
So that's the only reason for the locking of the login?If you have the 'Enforce Password Policy' checked and that user then puts the password in incorrectly more times than your Network password policy it will lock out the id.
Ok. But what's the reason login was not getting unlocked even after using t-sql? I had to then fail over the instance to other node to get it unlocked.
October 18, 2012 at 8:40 am
sqldba4u (10/18/2012)
Markus (10/18/2012)
sqldba4u (10/18/2012)
So that's the only reason for the locking of the login?If you have the 'Enforce Password Policy' checked and that user then puts the password in incorrectly more times than your Network password policy it will lock out the id.
Ok. But what's the reason login was not getting unlocked even after using t-sql? I had to then fail over the instance to other node to get it unlocked.
That I cannot answer at all. Failing over SQL Server shouldn't have any effect on logins and being locked out.
October 18, 2012 at 1:17 pm
If the password policies are different on each node - that could effect how soon that account would be unlocked. Or, it was just the fact that logins will be disabled for xx amount of time (by policy) before being automatically unlocked - and that time limit occurred at the same time as your failover.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply