April 28, 2009 at 12:09 pm
When CHECK_POLICY is on, and the server has a policy that does not allow the same password to be used for X changes, is this policy also enforced?
I do not have access to the server policy but assume it enforces unique passwords (this is true for my personal account). I created a SQL account with an initial password, changed the password to something else, then changed it back to the first password. SQL allowed me.
My tests make me think that SQL does NOT enforce password history. If it did, how would it track the password history to know?
Am I correct?
Thanks...JohnC
April 28, 2009 at 12:37 pm
Password history is enforced. It maintains a password history:
K. Brian Kelley
@kbriankelley
April 28, 2009 at 2:19 pm
If I change the password using SSMS does the password history apply?
It would appear I can use to the same password over and over when I right-click get properties on the account. I assumed the history would apply no matter how I changed the password so perhaps I assumed incorrectly...
Thanks...JohnC
April 28, 2009 at 2:27 pm
It might have to do with your permissions. Are you changing it using a sysadmin level account? Because in AD, as a domain admin that password history doesn't apply to me when I change a password.
K. Brian Kelley
@kbriankelley
April 28, 2009 at 2:57 pm
I am changing it using a domain account in the SYSADMIN role but not a domain admin. I also logged in with the SQL account which is in the securityadmin role.
If I can change the password, it appears I can change it to the same passward any time via SSMS...
Thanks...JohnC
April 28, 2009 at 3:03 pm
The enforcement should be based on the base OS.
If you are working with Windows Auth and SQL Auth, there're not related.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 29, 2009 at 5:02 am
Does that mean if I connect to an instance with a domain account (w/SA rights) and change the password of a SQL account, the policy is not checked? Doesn't seem right since if I try to use a password that does not meet the policy I get an error.
April 29, 2009 at 9:28 am
I used Domain Admins and Active Directory as a parallel security structure.
In Active Directory, within a domain, the Domain Admins group is king. Therefore, a member of the Domain Admins can break the rules.
In SQL Server, the sysadmin fixed server role is king. Therefore, a member of the sysadmin fixed server role can break the rules.
Therefore, if you are using a member of the sysadmin fixed server role to make the password change, you are allowed to break the rules.
K. Brian Kelley
@kbriankelley
April 29, 2009 at 9:49 am
Brian is the expert, and he's correct. For example, just like the domain administrator account cannot be locked out, the SA account cannot be locked out.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 29, 2009 at 10:07 am
I am far from an expert but can be like a bulldog refusing to let go. I apologize if I'm beating a dead horse, but,
I used my SYADMIN account to change the SQL account password to 'shortpw'. It failed with an error: 'Password validation failed. The password does not meet windows policy because it is too short'. It would appear I cannot violate that rule.
Another piece to the puzzle.
If I issue this command (with my SYSADMIN account), I can change the password to one I've used before:
ALTER LOGIN sectest WITH PASSWORD=N'UsedthisPasswordb4!'
If I issue this command (with my SYSADMIN account), I get this error:
ALTER LOGIN sectest WITH PASSWORD=N'UsedthisPasswordb4!' old_password =N'Changed2this!'
Msg 15114, Level 16, State 1, Line 1
Password validation failed. The password for the user is too recent to change.
When I change the password in SSMS I don't supply the old password just like the command that works.
It would appear supplying the old_password causes the policy to be enforced.
April 29, 2009 at 11:11 am
I thought about it after I posted but forgot to follow up. Password complexity and minimum length will still be enforced. It is within Active Directory, too. Those determine whether or not a given combination of letters, numbers, and characters are a valid password.
Anything with relation to password age can be bypassed. This includes the policy on how much time must occur before the password can be changed again.
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply