January 14, 2010 at 9:18 am
It is a long sordid tale that no doubt will not meet approval, but all I can say is I have to do as I am told and once the auditors approve of a process nobody wants to change it.
Long story short, we implemented a process by which we update the sa password (and another added login) every 90 days on hundreds of SQL servers. This works fine in SQL 2000, and in general works in 2005 as well ( I tweaked the script a bit in regards to the SQL Agent properties which no longer have to be updated with the new sa password).
However, on the three SQL 2005 system we have in the field the sa account got locked out at one point or another somehow. Most likely a vendor was trying to support issues on their database and tried to login several times as sa (should they have been using sa? No, but they would have tried it I assure you).
We have no other access, we locked out the Windows Admin group and the other login is restricted and cannot unlock the sa account.
I found a way around this that involves going into single user mode etc., but going forward it would be wonderful if I could just PREVENT the sa user from being locked. I cannot find a way to do it! I already tried uncheckng the enforce password policy and enforce password expiration but it had no effect. I know, I know, if it gets locked that is good, perhaps someone was trying to hack it etc., but at this point I am stuck with what I have and just need it to NOT get locked out regardless.
Is it possible?
Thanks ahead of time!
January 14, 2010 at 12:17 pm
You could do this:
1. Create another admin login and use that one, so that it won’t matter if the SA login is locked out.
2. Change the name of the SA login to something else that only you know, and create a new dummy SA account that has no privileges, so that even if it gets locked out, it doesn’t hurt anything.
January 14, 2010 at 2:03 pm
Thanks for the input. Those ideas are potential workarounds, but I take it you could not find a way to keep the SA user from being locked out either.
If it is not possible that is fine, I just want to know for sure.
Thanks,
July 22, 2010 at 1:52 am
I was having the same problem. And even when i tried to unlock it, the lockout would reactivate itself. So i found a way,
1 - Login with Windows Authentication (assuming its turned on): And go to Security --> Logins --> sa --> Properties --> uncheck: Enforce password policy.
2 - Resset your SA password
3 - Login with your SA account.
Thats it.
July 23, 2010 at 12:56 pm
We don't use sa account at all and have deleted that account! We do have a Windows Group that has sa priviledges. So the few members of that group do all the administration and troubleshooting of SQL Server.
July 23, 2010 at 1:38 pm
I did not think that just unchecking that option bit worked initially, but afer further research it does seem to do the trick!
If it were up to me we would not be using sa at all, and would correctly configure our Windows AD groups and use those, however, a certain process was approved by the auditors and my boss has absolutely no desire to go through that even for a much better process.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply