In the previous introduction article we have described concepts, terms, and common Policy Based Management tasks. The SQL Server Policy Based Management feature can be used in various scenarios and for different purposes. Using once declared policies and the Export/Import feature, maintaining SQL Server security in large enterprises with a number of SQL Server instances can be an automated process

In this article, we are going to describe a step-by-step process of creating appropriate policies for the login Password expiration enabled and database user Authentication type properties consistency evaluation as aspects of SQL Server security

Note that the whole process of creating and evaluating policies can be done using either T-SQL or SQL Server Management Studio. To make this article more descriptive we’ll focus on SQL Server Management Studio. However, to check appropriate T-SQL, you can use the Script Policy as and Script Condition as options in the context menu of corresponding Policy Based Management objects in Object Explorer

Each policy primary depends on the condition that will be used. Besides the condition, the policy is additionally defined against target objects

Conditions creation

To create a condition:

1. Start SQL Server Management Studio and expand the Management node. Expand the Policy Management node and open the context menu of the Conditions sub-node. Select the New Condition option
2. The Create New Condition dialog will open. Type in the name of the new condition (e.g. ConditionPasswordExpirationEnabled). From the Facets dropdown menu select the one which contains a property we want to create condition for. In our case it is the Login facet. Note that each condition can use one facet only
3. In the Expression grid, set the Field, Operator, and Value dropdown options to the following values: @PasswordExpirationEnabled, =, and True. This means that the condition will check whether the Password expiration enabled property is set on particular login entities

4. Click OK to save the condition

   

5. Similarly, using appropriate steps (1-4), create another condition (e.g. ConditionDatabaseAuthenticationType) using the User Options facet, @LoginType field, ‘=’ operator, and WindowsUser value

   

   The condition will test whether a user’s login type is using the Windows authentication

The newly created conditions will be shown under the Conditions node in Object Explorer

Policies creation

After the needed conditions are created, we can proceed with declaring and creating policies. To create a policy:

1. Select the New Policy option in the context menu of the Policies node
2. This will open the Create New Policy dialog. Type in the name for the policy (e.g. PolicyLoginPasswordExpiration)

3. Select previously created ConditionPasswordExpirationEnabled condition using the Check condition dropdown menu. Note that the dropdown menu provides conditions sorted per used facets during their creation. We have used the Login and User Options facets, and our conditions are listed under these facets. This way you can to quickly locate needed condition

   

4. The Against Targets box will automatically show the Every Login target. The target can be additionally customized, for instance to check only the logins with the name that matches specific pattern. We’ll explain the customization in further text in context of another policy

   

5. The next step is to define the policy evaluation mode. Default is the On demand mode. However, we will choose the On schedule mode via the Evaluation Mode dropdown menu. Click the New button to open a common SQL Server Agent job schedule dialog. After you define the schedule, it will be shown under its name in the Schedule box (e.g. MorningCheck). You can also use the Pick option to open the list of available schedules that already exist on the SQL Server instance

   Using the Server restriction option, you can specify, for instance, that the policy is applicable to specific SQL Server instances (e.g. SQL Server 2012 and newer versions)

6. Click the OK button to save the policy

Similarly, using the same steps (1-3), create another policy (e.g. PolicyDatabaseAuthenticationType) with the ConditionDatabaseAuthenticationType condition. Note that there are two targets in the Against Targets box now. There are the Every User and Every Database targets since user entities exist in multiple SQL Server database objects. This time, we are going to customize targets and narrow down their scope by specifying a match pattern for database names (e.g. database name starts with ACME):

1. Open the Every dropdown menu and select the New condition option, as shown below

   

2. This will open the Create New Condition dialog we have previously described. However, this time we’ll use it to declare the condition which will narrow down the targets in the policy

3. Type in the condition name (e.g. OnlyACMEDBs), leave the Database facet value (it’s set by default since we are creating the condition on databases), and set the expression to @Name LIKE ‘ACME%’ using the Expression grid, as shown below

   

4. To save the condition and return to the PolicyDatabaseAuthenticationType policy creation dialog click OK

5. The Create New Policy (PolicyDatabaseAuthenticationType) dialog will look like this:

   

   This time, we’ll leave the Evaluation mode option with the default On demand value. To save the policy click OK

Once the policies are created, they are shown under the Policies node in Object Explorer. Note that the OnlyACMEDBs condition is also available for further use

If needed, each condition or policy can be modified by selecting the Properties context menu option. Also, policies can be exported/imported from/to SQL Server instances (via context menu in Object Explorer) and instantly applied ensuring security consistency in the enterprise

Policy evaluation

Policies can be evaluated per their evaluation mode (as described in the previous article). In our example we have created two policies. One is set to be automatically evaluated based on the schedule, and the other one is set to be evaluated on user’s demand

Whatever the policy evaluation mode is used, the policy can always be evaluated manually. To do that select the Evaluate context menu option of the specific policy (e.g. PolicyLoginPasswordExpiration). The Evaluate Policies dialog will open

The dialog will provide the evaluation result for each target object in the Target details grid. In our example one login complies with the policy rule, while the other one does not

To apply the policy to the target that doesn’t comply the rule, check the box next to the target item in the grid and click the Apply button. The policy will modify the target login according to the rule (in our case it will set the login’s Password expiration enabled property). The dialog will automatically re-evaluate the policy:

In this article, we have described the necessary steps to create policy conditions, policies that use the conditions, and how to evaluate the policies against specific target objects. Finally, we described how the particular target object that does not comply with the policy can be automatically fixed. Policy Based Management is a powerful SQL Server feature that helps ensuring security across enterprise by applying declared values to SQL Server security related properties, both on SQL Server instances and their objects. Moreover, it provides ways to evaluate policies on target objects and apply the policies where required