Password policies checked by CHECK

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dr. Diana Dee » Password policies checked by CHECK_POLICY

26 posts, Page 3 of 3

«««1 2 3

Password policies checked by CHECK_POLICY

Rate Topic

Display Mode

Topic Options

Author

Message

David in .AU

Posted Monday, November 24, 2008 3:27 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543

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

Post #607918

Dr. Diana Dee

Posted Monday, November 24, 2008 3:42 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, April 19, 2013 3:02 PM
Points: 2,768, Visits: 108

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.

) BigGrin

Post #607928

David in .AU

Posted Monday, November 24, 2008 4:17 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543

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

Post #607943

Dr. Diana Dee

Posted Monday, November 24, 2008 5:54 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, April 19, 2013 3:02 PM
Points: 2,768, Visits: 108

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.

) BigGrin

Post #607979

David in .AU

Posted Monday, November 24, 2008 8:07 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543

Cool.

Yup, BOL needs that "more info needed" button alright :)

Post #608003

L' Eomot Inversé

Posted Tuesday, December 07, 2010 12:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 7,079, Visits: 7,121

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1031454

« Prev Topic | Next Topic »

26 posts, Page 3 of 3

«««1 2 3

Permissions