November 24, 2008 at 1:41 pm
Thank you for the reference. I had not been able to find any articles that were so definitive about which password policies went with which login option.
):-D
November 24, 2008 at 2:05 pm
Hi,
I also disagree like others here, this is what I found on the net:
There are two password options for SQL Server logins: CHECK_EXPIRATION and CHECK_POLICY. CHECK_EXPIRATION encompasses minimum and maximum password age, and CHECK_POLICY encompasses all the other policies. When you run afoul of either policy, the SQL Server login must be unlocked by the DBA, as shown shortly in an example.
//SUN
November 24, 2008 at 2:18 pm
Is your URL source different from and later than that posted by Chad Crawford? His dates from February 2005.
):-D
November 24, 2008 at 2:39 pm
Per Books Online under the section headed Password Policy
Policy Enforcement
The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:
When CHECK_POLICY is changed to ON, the following behaviors occur:
CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
The password history is initialized with the value of the current password hash.
What it doesn't mention is whether complexity is also checked, but I have the suspicion that may be default behaviour.
-d
November 24, 2008 at 3:17 pm
Books Online never said which policies were associated with which login option. That's why I performed the experiment.
):-D
November 24, 2008 at 3:27 pm
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 🙂
-d
November 24, 2008 at 3:42 pm
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
November 24, 2008 at 4:17 pm
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
November 24, 2008 at 5:54 pm
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
November 24, 2008 at 8:07 pm
Cool.
Yup, BOL needs that "more info needed" button alright 🙂
December 7, 2010 at 12:28 pm
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply