SQLChicken.com

Policy-Based Management and Local Password Policy

This post is based on an interesting question/situation that was posted over at ASKSSC.com today. The user asked how to create a policy condition that enforces local sql accounts to adhere to password expiration policies.

First off, to create the condition itself is relatively easy. Below I’ve provided the T-SQL code so that you can create the condition that way. I’ve also included a quick list on how to create it via SSMS GUI.

T-SQL method:

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

SSMS method:

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!

Now we’re left with another question: Where does this password policy come from? For details on that you can refer to the Books Online article about it (link). If your box is on a domain that has Active Directory policies regarding password expiration, when you select the box for ‘Enforce password policy’ as well as ‘Enforce password expiration’, these settings will come from that policy. Don’t have an Active Directory policy? No problem! If a policy isn’t provided from Active Directory, Windows then looks to its local security policies for these values.

To see the local values, click on your Start button, then type in ‘secpol.msc’ (don’t type type the single-quotes). This will open up the Local Security Policy MMC Snap-in. Expand the Account Policies folder and then click on the Password Policy folder. In the right side pane you will see the various password-related options you can set such as Maximum password age or password length.

While policy-based management can help you check whether or not the accounts have the option enabled to enforce the policy checks, Policy-based management itself has not bearing on the Local Security Policy settings. This is something you, as an administrator, will have to set and configure outside of SQL Server.

Find Table Heaps Using Policy-Based Management

This is just a quick post in regards to a conversation I just had via Twitter. If you don’t already use Twitter, the SQL Community has setup a great resource on there using the hashtag of #sqlhelp.

Today a conversation came up due to a forum question over at SQLServerCentral regarding applying policies to databases with tables that have heaps. If you’re not familiar with the term, a heap is a table that has no clustered index on it. This can be problematic from a performance stand point so it might benefit you to find a way to identify these potential problem children. Enter Policy-Based Management.

This is a simple policy that you can run against your servers and it will identify your tables that are heaps. Just to clarify this policy identifies if your table has a clustered index on it. If it doesn’t then it will fail policy check. I’ve provided two ways to get the policy.

Download policy by clicking here

OR (Updated 4/15/11 to include creation script for condition)

--CREATE CONDITION

Declare @condition_id intEXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Find heaps', @description=N'', @facet=N'Table', @expression=N'<Operator>  <TypeClass>Bool</TypeClass>  <OpType>EQ</OpType>  <Count>2</Count>  <Attribute>    <TypeClass>Bool</TypeClass>    <Name>HasClusteredIndex</Name>  </Attribute>  <Function>    <TypeClass>Bool</TypeClass>    <FunctionType>True</FunctionType>    <ReturnType>Bool</ReturnType>    <Count>0</Count>  </Function></Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUTSelect @condition_id
GO

--CREATE POLICY
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Find Table Heaps_ObjectSet', @facet=N'Table', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Find Table Heaps_ObjectSet', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Find Table Heaps', @condition_name=N'Find heaps', @policy_category=N'', @description=N'Heaps are tables without clustered indexes. Read the link below to learn more about heaps.', @help_text=N'Fragmentation (part 4):what are heaps? by Paul Randal', @help_link=N'http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Find Table Heaps_ObjectSet'
Select @policy_id

GO