none
Central Policy

    Question

  • Dear Sir,

    I have sql2012, can i prevent opening login user without mandatory policy. Where the policy must run on the sql manager not on user base.

    Thank u for your help

    Monday, April 28, 2014 10:17 AM

Answers

  • Hmmm yes. This will create a new policy and a condition which is visible under Management->Policy Based Mangement->Policies and Conditions. But also this would restrict creation of any new Windows Login as by default they are Policy Enforced. If you will try to create a new sql login with any of the policies checked, an error would occur.

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    • Marked as answer by Wael Shami Monday, April 28, 2014 2:22 PM
    Monday, April 28, 2014 2:18 PM

All replies

  • Hello,

    You question is not clear for me, may can you explain it more detailed, please?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, April 28, 2014 10:46 AM
  • Hi Olaf,

    I want to prevent opening new sql user without mandatory policy. Ex: 

    1. Enforce password

    2. Enforce expiration


    Monday, April 28, 2014 10:51 AM
  • I guess you mean create and not open. You can, just uncheck those boxes for mandatory policies. You should be able to create the login.

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Monday, April 28, 2014 11:12 AM
  • Hi Olaf,

    I want to prevent opening new sql user without mandatory policy. Ex: 

    1. Enforce password

    2. Enforce expiration


    Still question is incorrect,there is nothing like opening SQL server user.You create SQL server user.Or are you saying that when  you create a login it should always follow password policy and force expiration is checked on.This option is only valid for SQL server login and you can check various boxes in GUI to attain this.Or you can use CHECK_POLICY = { ON | OFF } in Tsql query

    http://technet.microsoft.com/en-us/library/ms189751.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 28, 2014 11:25 AM
  • Yes, i need to add a policy that prevent disabling (password,expiration) policy, when creating new sql login account.
    Monday, April 28, 2014 11:42 AM
  • Yes, i need to add a policy that prevent disabling (password,expiration) policy, when creating new sql login account.

    Passowrd poliy needs to be set at windows level .Please get in touch with windows team.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 28, 2014 12:31 PM
  • Hi Wael,

    This can be accomplished by Policy Based Management. I scripted out the policy and condition and pasted it below.

    All that needs to be done is that create a new policy, then create a new condition with the below details:-

    Condition Facet:- Login Options

    Fields:

    @PasswordPolicyEnforced = False
    @MustChangePassword = False
    @PasswordExpirationEnabled = False

    No one would be able to create a new sql/windows login with password policies enabled.

    --Condition to Check for Login Options
    Declare @condition_id int
    EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Login', @description=N'', @facet=N'ILoginOptions', @expression=N'<Operator>
      <TypeClass>Bool</TypeClass>
      <OpType>AND</OpType>
      <Count>2</Count>
      <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>MustChangePassword</Name>
          </Attribute>
          <Function>
            <TypeClass>Bool</TypeClass>
            <FunctionType>False</FunctionType>
            <ReturnType>Bool</ReturnType>
            <Count>0</Count>
          </Function>
        </Operator>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>EQ</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>Bool</TypeClass>
            <Name>PasswordExpirationEnabled</Name>
          </Attribute>
          <Function>
            <TypeClass>Bool</TypeClass>
            <FunctionType>False</FunctionType>
            <ReturnType>Bool</ReturnType>
            <Count>0</Count>
          </Function>
        </Operator>
      </Operator>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>EQ</OpType>
        <Count>2</Count>
        <Attribute>
          <TypeClass>Bool</TypeClass>
          <Name>PasswordPolicyEnforced</Name>
        </Attribute>
        <Function>
          <TypeClass>Bool</TypeClass>
          <FunctionType>False</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

    --Policy to be evaluated
    Declare @object_set_id int
    EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Login Chec k_ObjectSet', @facet=N'ILoginOptions', @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'Login Chec k_ObjectSet', @type_skeleton=N'Server/Login', @type=N'LOGIN', @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/Login', @level_name=N'Login', @condition_name=N'', @target_set_level_id=0
    GO

    Declare @policy_id int
    EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Login Chec k', @condition_name=N'Login', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Login Chec k_ObjectSet'
    Select @policy_id
    GO


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Monday, April 28, 2014 1:30 PM
  • Hi Kartar,

    Sorry for late response. Do you mean i need to run all this query?

    Monday, April 28, 2014 2:11 PM
  • Hmmm yes. This will create a new policy and a condition which is visible under Management->Policy Based Mangement->Policies and Conditions. But also this would restrict creation of any new Windows Login as by default they are Policy Enforced. If you will try to create a new sql login with any of the policies checked, an error would occur.

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    • Marked as answer by Wael Shami Monday, April 28, 2014 2:22 PM
    Monday, April 28, 2014 2:18 PM
  • OK thank u Kartar
    Monday, April 28, 2014 2:22 PM