January 14, 2010 at 2:54 pm
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 somehow got locked out at one point or another. 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 already 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 7:53 pm
The lockout policy is controlled through the AD policy. If AD is not present, then it will use the Local machine policy. Try editing that policy - however that could leave all local accounts on the machine exposed for hacking.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 15, 2010 at 8:27 am
Wow, so there is not setting within MSSQL then. I guess that explains why I could not find it!
I am a little surprised that it is so intergrated with the OS that the internal logins such as SA can be governed by the same rules pushed down by Active Directory and the domain.
I had someone tell me once that SQL 2005 the logins are actually windows users, but I was not able to find them under local users and thought the person was full of it. Perhaps it is not that simple.
Anyway, it soulds like there is little I can do about under my current conditions (since I am not a domain administrator).
I guess we will be taking them down from time to time to put things in single user mode and hack around the lack of an SA user to unlock it. Bummer!
Thanks so much for the answer though, and I lover your handle, very witty.
January 15, 2010 at 10:49 am
I saw posted in another thread the suggestion of Creating an alternative user to replace SA. Leave SA in place - but decrease its permissions.
That might assist you somewhat with the lockout issue. The new user would be a UserName that only the DB Group knows - fairly long in char length, with a password to match.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 15, 2010 at 11:10 am
Thanks, techincally we should have done that from the get go, but hindsight as they say...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply