Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Can I restrict targets to SQL logins for a 2008 policy that forces the “Enforce Password Policy” property to be set when a security login is added?

Unanswered Can I restrict targets to SQL logins for a 2008 policy that forces the “Enforce Password Policy” property to be set when a security login is added?

  • Wednesday, January 25, 2012 7:04 PM
     
     
    Sign In to Vote
    0

    Can I restrict targets to SQL logins for a 2008 policy that forces the “Enforce Password Policy” property to be set when a security login is added?

    I have a Policy which forces the “Enforce Password Policy” property to be set when a security login is added, but it prevents creation of Windows accounts

    When I open the policy, then click on the list next to Every in the Against Targets sub-window, then try to change it to a LoginIsNotWindowsUserOrGroupOrSpecialAccount Condition I have created, I get the following error

    Operation Alter failed during execution - Invalid target filer: Login.  Only filters that restrict the first level below the Server node are allowed - Error:34016

     

     

    Porter Horne
     

All Replies

  • Friday, January 27, 2012 3:25 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Porter,

    Could you please check the definition of the LoginIsNotWindowsUserOrGroupOrSpecialAccount condition, which is something like:

    Name: LoginIsNotWindowsUserOrGroupOrSpecialAccount
    Facet: Login
    Expression: (@LoginType not equal to Windows User) AND (@LoginType not equal to Windows Group) AND
    (@Name not like '##%') 

    Please pay attention to this blog with the similar scenario about implement policy only take effect on SQL Server login:
    SQL Server 2008 Security - Policy Example. You are required to change the ServerLoginPasswordOlderThan70Days condition with the expression to '@PasswordPolicyEnforced= ture’.

    Stephanie Lv

    TechNet Community Support

     
  • Monday, January 30, 2012 6:31 PM
     
     
    Sign In to Vote
    0

    Hi Stephanie,

    The link to Guarev's blog is what started this journey.  He sent me to post here.

    LoginIsNotWindowsUserOrGroupOrSpecialAccount is exactly as you show

    Porter

    Porter Horne
     
  • Tuesday, January 31, 2012 1:16 AM
    Moderator
     
     
    Sign In to Vote
    0

    Porter,

    I have tested the sample on my envirnment with success.  Could you please double-check the expressions if they are configured correctly.

    Stephanie Lv

    TechNet Community Support

     
  • Tuesday, February 07, 2012 6:03 PM
     
     
    Sign In to Vote
    0
    Please provide details as to what you have tested, as I cannot change Every in the Against Targets sub-window

    Porter Horne

     
  • Wednesday, February 08, 2012 1:50 AM
    Moderator
     
     
    Sign In to Vote
    0

    Porter,
    What I have tested is as the screen shorts below:

    1. Create a condition which is used to enforce the password policy, with the Login Fact.condition
    2. Create another condition which excludes login types other than SQL Server login, with the login Fact. condition2
    3. Create the policy.

    policy

    Please make sure you have select 'Login' as the Fact for both two conditions.

    Stephanie Lv

    TechNet Community Support

     
  • Wednesday, February 08, 2012 3:27 PM
     
     
    Sign In to Vote
    0

    I have to use the “LoginOption” facet in place of “Login” as “LoginOption” allows the ability to evaluate on change, which can prevent creation, but “Login” will only allow detection of already existing logins that do not enforce password complexity

    Porter Horne

     
  • Tuesday, February 14, 2012 9:16 AM
     
     
    Sign In to Vote
    0

    It's by design. On change mode is allowed only if target filter is a “name condition”. More information about the restriction on evaluation mode you can find here: http://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx

    In your case you can only use On demand and On schedule evaluation  mode.

    Ana Mihalj