Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Password policies checked by CHECK_POLICY Expand / Collapse
Author
Message
Posted Monday, November 24, 2008 3:27 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
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
Posted Monday, November 24, 2008 3:42 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:47 PM
Points: 3,143, Visits: 136
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.

)
Post #607928
Posted Monday, November 24, 2008 4:17 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
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
Posted Monday, November 24, 2008 5:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:47 PM
Points: 3,143, Visits: 136
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.

)
Post #607979
Posted Monday, November 24, 2008 8:07 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
Cool.

Yup, BOL needs that "more info needed" button alright :)
Post #608003
Posted Tuesday, December 7, 2010 12:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:53 PM
Points: 8,543, Visits: 9,030
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
Post #1031454
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse