x

How to create custom password policy - Policy based management

I was asked to come out with the policy in such away that only sql server logins password expiries every 90 days can anyone help me in scripting this in a condition?

On the web i found only few screen shots how to create a policy.....
more ▼

asked May 04 '11 at 12:54 PM in Default

ak1516 gravatar image

ak1516
61 5 6 6

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Are you referring to Windows authentication? If so, the AD administrator controls how long the password reset must occur. With PBM you can, however, make sure that the account you add to the server has password expiration enabled as well as enforcing your AD's password policy. Here is the T-SQL to create this condition:

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition 
    @name=N'Password Policy Enforced', @description=N'', @facet=N'Login', 
    @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>EQ</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>Bool</TypeClass>
      <Name>PasswordExpirationEnabled</Name>
    </Attribute>
    <Function>
      <TypeClass>Bool</TypeClass>
      <FunctionType>True</FunctionType>
      <ReturnType>Bool</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>EQ</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>Bool</TypeClass>
      <Name>PasswordPolicyEnforced</Name>
    </Attribute>
    <Function>
      <TypeClass>Bool</TypeClass>
      <FunctionType>True</FunctionType>
      <ReturnType>Bool</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', 
@condition_id=@condition_id OUTPUT

Select @condition_id

GO

You can run this T-SQL and it will create the condition. Once its created, you can open it in the GUI (the condition will appear in the conditions folder under Policy-Based Management node in SSMS).

If you'd like a comprehensive resource on PBM, check out the book Pro SQL Server 2008 Policy-Based Management (I co-authored it).
more ▼

answered May 04 '11 at 01:01 PM

Jorge Segarra gravatar image

Jorge Segarra
419 2

Just a quick run-down of how I came up with that code above:

  1. Under PBM node, right-click conditions folder and select New Condition
  2. Name your new condition something useful
  3. Select Login facet from drop-down menu
  4. Click on field box and select @PasswordExpirationEnabled from properties list
  5. Set the operator value to True
  6. Click on 'Click here to add clause' to add another clause to policy
  7. Click on field box and select @PasswordPolicyEnforced from properties list
  8. Set the operator value to True
  9. Click OK. You've now just created a new condition!
To use this in a policy just right-click policy folder, select new policy and from the condition drop down select the condition you created in the above steps.
May 04 '11 at 01:07 PM Jorge Segarra
@Jorge Segarra I formatted your script, hope you don't mind :)
May 04 '11 at 01:07 PM Oleg
i have the windows accounts to run the services only and the expiration of that is set to one year. I am using sql server authentication for users connectivity and application connectivity.I am assigned the task to impliment password policy using PBM on sql server authenticated accounts only.
May 04 '11 at 01:08 PM ak1516
In that case, follow the condition steps I outlined above and add one more clause. You want to select the property of @LoginType = SqlLogin
May 04 '11 at 01:10 PM Jorge Segarra
Excellent, thank you Oleg!
May 04 '11 at 01:10 PM Jorge Segarra
(comments are locked)
10|1200 characters needed characters left
FYI I wrote up a blog post summarizing this setup, hope it helps: http://sqlchicken.com/2011/05/policy-based-management-and-local-password-policy/
more ▼

answered May 04 '11 at 01:58 PM

Jorge Segarra gravatar image

Jorge Segarra
419 2

(comments are locked)
10|1200 characters needed characters left
Update to this post: You can use extended properties on sql logins to do what you were asking for here. I wrote up a post last night about expiring databases but you can easily change the condition to look at extended properties on logins as well: http://sqlchicken.com/2011/10/expiring-databases-and-policy-based-management/
more ▼

answered Oct 06 '11 at 12:42 PM

Jorge Segarra gravatar image

Jorge Segarra
419 2

(comments are locked)
10|1200 characters needed characters left
Where are we setting the logic saying password expires in 90 days?...sorry for asking like this... I am new bee to PBM
more ▼

answered May 04 '11 at 01:12 PM

ak1516 gravatar image

ak1516
61 5 6 6

Hehe no apologies needed! That is set when you create the sql login itself. Refer to the BOL page on password policy http://msdn.microsoft.com/en-us/library/ms161959.aspx
May 04 '11 at 01:15 PM Jorge Segarra
(comments are locked)
10|1200 characters needed characters left

Ok I think i might of explained my problem wrong.Is it possible to create a custom policy? If i check the policy when creating the account it uses my windows password policy but i do not want that to happen for the sql logins. Having a separate custom policy for the sql logins would be helpful.

more ▼

answered May 04 '11 at 01:26 PM

ak1516 gravatar image

ak1516
61 5 6 6

Ah, in that case no, not really. While the feature is called "Policy-Based Management", what you're looking for is Windows-based. The password policy for local SQL accounts is keyed off of either the local security policy (on the Windows box the SQL Server resides on), or the overall password policy set via Active Directory policies.
May 04 '11 at 01:30 PM Jorge Segarra
I am trying to implement policy based management feature for sql logins.Is there a way ?
May 04 '11 at 01:34 PM ak1516
Policy-based management works with sql logins but not for what you're asking for. The password expiration policy is a Windows setting. Policy-based management is aimed towards SQL Server objects and settings, not Windows.
May 04 '11 at 01:48 PM Jorge Segarra
Okie i am looking for sql logins only.
May 04 '11 at 01:56 PM ak1516
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x6

asked: May 04 '11 at 12:54 PM

Seen: 3284 times

Last Updated: May 17 '13 at 02:07 AM