SQL Server Policy Based Management provides means to declare certain states and properties for SQL Server instances and their objects in a form of policies. These policies can be evaluated against a set of SQL Server instances, or against a specific SQL Server instance and a set of its objects that can be narrowed even more using additional policy conditions. However, even an evaluation of declared policies against their targets (SQL Server instances and their objects) produces viable results, in a form of evaluation reports that shows which target does and which one does not comply with the policy, this is not enough. After non-compliant targets are identified, they need to be altered in order to comply with the evaluated policies.

There is a range of targets used by policies in SQL Server and their availability in a policy condition depends on a used facet. For example, SQL Server logins are available as targets if the Login Options facet is used for declaring the policy condition. Similarly, SQL Server databases can be used as policy targets when the Database Maintenance facet is used for the condition.

Each facet provides its own set of targets. A target can be included within multiple facets and one facet can include multiple target types. For example, the Database target is managed by the Database, Database Maintenance, Database Options, Database Performance, and Database Security facets.

Identified Non-compliant targets evaluated by certain policies can be altered manually or automatically in order to comply with a declared policy.

Identifying non-compliant policy targets

Policy Based Management policies are defined with an appropriate condition and target SQL Server instances and/or their objects. In the following example, we are going to create few policies to demonstrate a common scenario that can occur after the process of identifying targets that do not comply with a policy is completed.

To create a Policy Based Management condition using SQL Server Management Studio:

1. Expand the Management and Policy Management nodes in Object Explorer, open the Conditions node context menu, and select the New Condition option.
2. Type in the new condition name in the Create New Condition dialog (e.g. Password Policy Enforced) and select the Login Options facet from the Facets drop-down list.
3. Using the Expression grid, specify the following condition:
   @PasswordPolicyEnforced = True

Using the described steps for creating a condition, declare another condition (e.g. Password Expiration Enabled) using the same facet (Login Options) with the following condition expression:
@PasswordExpirationEnabled = True

To create appropriate policies that utilize the conditions we have previously declared:

1. Use Object Explorer to expand the Management and Policy Management nodes, open the Policies context menu, and select the New Policy option.

2. Type in the new policy name in the Create New Policy dialog (e.g. SQL Login Password Expiration) and select the Password Expiration Enabled condition from the Check condition drop-down list. Note that the list of conditions is sorted and shown by the used facets, so the Password Expiration Enabled condition is located under the Login Options facet node.

   

   This can be very useful if you have a large number of declared conditions on SQL Server instance.

3. The Every Login target will be shown in the Against targets box by default. Since we are going to examine specific SQL Server login properties (the Enforce password policy and Enforce password expiration) tied to the SQL Server authentication mode, we are going to alter the policy target to exclude logins with Windows authentication from a policy evaluation result.
4. Click Every in the Against targets box and select the New condition option. Use the Create New Condition dialog, similarly as we described, to create an additional condition (e.g. SQL Server Authenticated Logins) based on the Login facet, and with the following expression:
   @LoginType = SqlLogin
5. Click OK to close the dialog and save the SQL Server Authenticated Logins condition.

6. The target of the SQL Login Password Expiration policy gets changed to SQL Server Authenticated Logins.

   

Using the described steps, create an additional policy (e.g. SQL Login Password Policy). Use the Password Policy Enforced condition for the Check condition value and the same SQL Server Authenticated Logins condition we created to narrow down the policy targets.

Declared policies check whether the Enforce password policy and Enforce password expiration options are set on SQL Server logins (the ones with SQL Server authentication).

Create a new SQL Server login (e.g. Jack) for the example testing purpose. Set the SQL Server authentication for the login and leave the Enforce password policy and Enforce password expiration options unchecked.

To evaluate the declared policies against their targets:

1. Select the Evaluate option from the SQL Login Password Expiration context menu.

2. The Evaluate Policies dialog will provide the result of the evaluation.

   

   As expected, the Jack login is identified as the policy non-compliant. This is the part where the non-compliant target object should be modified in order to comply with the policy. This can done manually, by changing the target object properties (in our case the login properties), or automatically.

Applying policies automatically

To alter the target object automatically, in order to comply with the policy:

1. Select the checkbox on the left side of the non-compliant target item in the Target details list. Note that if no targets are checked, the Apply button is disabled.
2. Click Apply.

3. Confirm the following dialog:

   

   SQL Server will try to apply appropriate change to the target login, but the following error will occur:

   

In our example, the policy could not be applied to the login as the login’s Enforce password expiration property depends on the Enforce password policy property which must be set prior to Enforce password expiration.

However, if you evaluate the SQL Login Password Policy first and apply the policy to the Jack login the same way as we described previously, the login will be altered properly and its Enforce password policy set. After the Enforce password policy property is set, start the evaluation of the SQL Login Password Expiration again and apply the policy to the login.

This time the policy will be successfully applied and the target login properly altered.

We have described how SQL Server Policy Based Management policies can be automatically applied to non-compliant targets, but it’s not uncommon that changes cannot be applied if order of evaluated polices is not proper. In our example we had to switch the policies evaluation sequence order.

However, even as Policy Based Management policies declare how a particular SQL Server instance or its objects should be configured, there are policies that cannot be automatically applied on evaluated targets. For instance, the @DataAndBackupOnSeparateLogicalVolumes property of the Database Maintenance facet cannot be automatically applied on SQL Server databases as it only checks whether data and backup files of a particular database reside on different logical volumes and not on which ones. Such non-compliant targets must be manually altered in order to comply with the policy.