SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Password policies checked by CHECK_POLICY


Password policies checked by CHECK_POLICY

Author
Message
David in .AU
David in .AU
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 561
One thing I think people need to be careful of is sequence.

If you set expiration off and then follow up with a set check policy on then the check policy will override the previous setting and re-enable expiration checks.

And I think BOL needs a button in it along the lines of "Article requires further clarification" or some such, it's pretty complete as far as online help is concerned, but there is the odd article that could use some work, this is one of them Smile

-d
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3431 Visits: 143
You can create a login with both CHECK_POLICY off and CHECK_EXPIRATION off.

My impression is that if you then (ALTER LOGIN) set CHECK_POLICY ON, that will not automatically set CHECK_EXPIRATION on.

):-D
David in .AU
David in .AU
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 561
Hrmm...

Either BOL is wrong or there is a bug (sorry, undocument feature) in SQL Server, either of which is possible.

One question I do have is whether the windows 2k3 password policies will override the sql expiration off setting.

specifically (from BOL):
CHECK_EXPIRATION = { ON | OFF }
Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.

CHECK_POLICY = { ON | OFF }
Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

As you can see, Check_Expiration makes no mention of the windows policy but Check_Policy says that it will enforce the windows policy; could it be that Expiration ON is only useful if you want to enforce this regardless of what the windows policy says? Did you test disabling expiration in the windows policy and then playing with the SQL expiration?

-d
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3431 Visits: 143
The wording in Books Online is quite misleading.

First of all, here is the URL for CREATE LOGIN (Transact-SQL) that has the information you quoted: http://technet.microsoft.com/en-us/library/ms189751.aspx

Here is the phrase I want to dissect: "CHECK_POLICY = { ON | OFF }
Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON."

The part of this item that is misleading is: "The password policies of the computer on which SQL Server is running should be enforced on this login."

That phrase could imply that ALL the password policies are enforced. However, this is not the case. It is only the password policies checked by CHECK_POLICY that are enforced. And CHECK_POLICY definitely does NOT check the Maximum password age policy.

I created a SQL login with CHECK_POLICY of ON and CHECK_EXPIRATION of OFF. I set the Maximum password age policy to 2 days. Three days later, the SQL login could connect to the database engine.

):-D
David in .AU
David in .AU
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 561
Cool.

Yup, BOL needs that "more info needed" button alright Smile
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26269 Visits: 12506
Nice question. Much discussion, which leaves me somewhat muddled: I thought Check_Policy covered everything except maximum age (which is what CHECK_EXPIRATION covers), but someone found a BoL entry that claims minimum age is also covered by CHECK-EXPIRATION which is very confusing (it doesn't on some windows versions, does it on any version or is this a BoL error?). The "reversible encryption" thing I just ignored - only insecure lunatics, unfortunates stuck with ancient legacy systems, and really unlucky people who are stuck with managers who think that passwords should be easily retrievable (ie the managers are insecure lunatics) would toch that even for Windows logins, so I couldn't imagine a policy option to reduce security by enforcing it for SQL logins.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search