The Policy Based Management feature was introduced in SQL Server 2008. The purpose of the feature is to assists SQL Server administrators in creating and enforcing policies tied to SQL Server instances and their objects. The policies can be configured on one SQL Server and re-used on other SQL Server instances to provide a SQL Server security model for instance

Policy Based Management allows DBAs to define the preferred state of the SQL Server system components (e.g. instances and objects) and compare the compliance status with the preferred state. Properly declared policies ensure enforcing company rules in the SQL Server environment, and are commonly a part of the SQL Server security model

The Policy Based Management feature is built on top of the SQL Server Management Objects collection (objects that are designed for programming all aspects of managing Microsoft SQL Server) which supports SQL Server 2000 and later versions. Therefore Policy Based Management can be utilized on versions prior to SQL Server 2008, for instance via the PowerShell subsystem and SQL Server Agent

Concepts of Policy Based Management

The feature has three concepts:

1. Policy management – policies are created and maintained by SQL Server policy administrators
2. Explicit administration – one or more managed targets are explicitly checked by administrators whether they comply with a specific policy, or explicitly modify the targets according to a policy
3. Evaluation modes – the modes are divided per type of the execution and the afterward action based on the policy evaluation result:
   1. On demand – the policy is evaluated when explicitly specified by the administrator
   2. On change: prevent – if selected, this automated mode will utilize DDL triggers in order to prevent violations of specific policy by rolling back the changes. Note that this evaluation mode requires the nested triggers option enabled on SQL Server. If that’s not the case, the evaluation will perform with errors or fail. To enable the nested triggers, open the Properties dialog for a particular SQL Server instance, navigate to the Advanced page, and set the Allow Triggers to Fire Others property to True
   3. On change: log only – the policy is automatically evaluated on changes that may violate the policy rules and event notifications are created
   4. On schedule – this evaluation mode uses SQL Server Agent jobs to automatically check policy violations per scheduled time

Automated evaluations may affect system performance, depending on the number of objects being evaluated against the particular policy. On the other hand, automated evaluation modes ensure consistent SQL Server security

Terms used in Policy Based Management

There are several terms used in the SQL Server Policy Based Management feature

• Policy Based Management managed target – an entity managed by Policy Based Management, such as a SQL Server instance, database, or table
• Policy Based Management facet – grouped logical properties that determine specific types of managed targets with their behavior or characteristics. One target can be managed by multiple facets and one facet can manage multiple target types. For example, the Database target is managed by the Database, Database Maintenance, Database Options, Database Performance, and Database Security facets

• Policy Based Management condition – a logical expression against which the specified target state is evaluated. For example, whether a password policy is enforced or not on login entities. One condition (expression) can consists of one or more logical sub-expressions

  

• Policy Based Management policy – a set of Policy Based Management condition, target, evaluation mode, and schedule. For example, a set of security rules specified for login entity, such as the MustChangePassword and PasswordExpirationEnabled properties. A policy can be enabled or disabled, depending on requirements. Note that one policy is limited to one condition

  

• Policy Based Management policy category – SQL Server offers creation of user defined policy categories. There are two types of policy categories for the servers and for the databases. Moreover, one policy can belong to one policy category only. The purpose of policy categories is to help in managing a large number of policies in enterprise environments

Common Policy Based Management tasks

There are several common tasks that need to be performed prior to evaluation of a SQL Server policy. First, you need to create the condition which will be used within the policy

Creating and modifying conditions

To create a new condition via SQL Server Management Studio, navigate to the Policy Management node in Object Explorer, expand the node and select the New Condition option from the Conditions sub-node context menu. This will open the Create New Condition dialog

The dialog provides following properties:

• Condition name – the one we’ll use later in the specific policy
• Facet dropdown menu – a list of all available facets to choose from. A condition is limited to one facet. For example, a condition cannot be used to specify properties both from the Login and Server Audit facets. However, there are shared properties between multiple facets such as the MustChangePassword property is shared with the Login and Login Options facets
• Expression – the grid used to define a logical expression against which policy target state will be evaluated

Once created, the condition will be shown under the Conditions node and can be additionally modified if needed. The next step is to create policies with appropriate conditions

Creating and modifying policies

To create a new policy, navigate to the Policy Management node in Object Explorer, expand the node and select the New Policy option from the Policies sub-node context menu. This will open the Create New Policy dialog where you can specify the name of the policy, select one of existing conditions, and specify the targets

Note that some conditions can only be applied to certain target types. For example, you cannot apply a condition based on the Database facet to login entities as targets. If no targets appear in the dialog, the check condition is defined at the server level

Finally, the dialog provides the evaluation mode selection. Choose one of the previously described evaluation modes appropriate to the created policy, e.g. the On demand evaluation mode if the policy will be evaluated manually

Once created policies are located in the Policies node and can be additionally edited

Exporting and importing policy

The exporting and importing policy features makes Policy Based Management transferable between SQL Server instances throughout enterprise environment. Each policy can be exported via its context menu (the Export Policy option) into an xml file. To import policy, select the appropriate option from the Policies node context menu. This will open the Import dialog that is used to specify one or more xml files, specify whether to replace duplicates, and set the state of imported items (Enabled, Disabled, or Preserve policy state)

Evaluating policies

Policies can be evaluated based on their evaluation mode as we previously described. To manually evaluate the policy, or enforce scheduled evaluation, select the Evaluate option in the context menu of the policy

This will open the results of the evaluation indicating all target objects that do not comply with the policy

Additionally, you can select the View option and check the exact cause of the evaluation failure

The policy evaluation feature provides a possibility to fix non-complying target property, but we’ll discuss that in the next article where we’ll provide a step-by-step Policy Based Management example that will ensure the login PasswordExpirationEnabled and database user AuthentiationType properties consistency as aspects of SQL Server security