Let’s look at the terms in policy-based management. They are target, facet, condition, policy, and category. Before creating a policy, you need to know what entities in SQL Server you want to apply the policy to. The entities are the “targets” of the policy. A target could be a SQL Server instance, a database, a table, or a login, etc. You can also apply filters to the targets in a server instance to get a target set, for example, all the user databases on an instance. Targets have their own logical properties. For example, a login has a name, a login type (SQL Server or Windows login), a default database and the enforcement of password policy. A set of logical properties that model the behavior or characteristics of a certain type of targets is called a “facet”. A target can have one or more facets. For example, a server target can have a configuration facet that includes the options configured by sp_configure, and an audit facet that includes the security login auditing settings on the server. We can define a Boolean expression that specifies the allowed states of the properties in a facet. This Boolean expression is called a “condition”. Once we have targets and a condition, we can define a “policy” to check the condition against the targets. We can also assign the policy to a user-defined “category”. Policy categories help manage the policies and facilitate database subscriptions to all the policies in a category instead of individual policies. Let’s show you how to use categories below.

Manage Policy Categories and Database Subscriptions

To manage policy categories in SSMS, expand Management in Object Explorer, right-click Policy Management, and then click Manage Categories.

A policy category can be enforced on all the databases or individual databases by turning on or off the “Mandate Database Subscriptions” option in the “Manage Policy Categories” dialog box. Assume that we are working on a Web application project named Matrix. This project communicates with a SQL Server backend database called Matrix. We define a set of policies that specifically apply to the security principals and database objects used in the Matrix project, and assign them to a category called “Matrix Policies”. Therefore, we only want to apply the policies in the “Matrix Policies” category to the Matrix database. In the “Manage Policy Categories” dialog box, we uncheck the “Mandate Database Subscription” option for the Matrix Policies category.

We can also run the msdb.dbo.sp_syspolicy_update_policy_category stored procedure to unset mandate subscription.

EXEC msdb.dbo.sp_syspolicy_update_policy_category @name=N'Matrix Policies', 
 @mandate_database_subscriptions=False

A database subscribes to a set of policy categories and only the policies in the subscribed categories can govern a database. In our example, we would like to subscribe the Matrix database to the Matrix Policies category. In Object Explorer, right click the Matrix database, point to Policies, and then click Categories. In the Categories dialog box, check the Matrix Policies category to subscribe.

Policy Execution Modes

A policy can be executed manually or automatically. The four possible execution modes are shown here.

• On Demand. This mode evaluates the policy only on demand of the user.
• On Change - Prevent. This automated mode uses DDL triggers to prevent policy violations.
• On Change - Log Only. This automated mode uses event notification to evaluate a policy when a relevant change occurs and logs policy violations.
• On Schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy. The mode logs policy violations.

Out of the four execution modes, the “On Change – Prevent” and “On Change – Log Only” modes are more useful. They help a DBA to catch or prevent incompliance in real time. In an enterprise environment, you would only evaluate a policy manually against hundreds or thousands of servers on one-off cases. We would like to show you an example of how to use the “On Change – Prevent” execution mode to prevent incompliance.

By default, the BUILTIN\Administrators group is a member of the sysadmin server role. Everyone in this group on a SQL Server host has the sysadmin rights on all the SQL Server instances on the host. However, in enterprise environments, system administrators and DBAs are usually in different groups, and have separate responsibilities. System administrators should not have access to SQL Server at all. We first need to remove the “BUILTIN\Administrators” login from SQL Server. Furthermore, we will create a policy to prevent the login to be added back in.

1. In SSMS, expand Management in Object Explorer, expand Policy Management, right click Conditions, and select New Condition. In the New Condition dialog box, provide a name “No server access” to the new policy. Select the Login facet. In the Expression area, in the Field box, select @HasAccess, in the Operator box select =, and in the Value box select False. This creates a condition to check if a login is granted access to the SQL Server.

2. We only want the new policy to be applied to the 'BUILTIN\Administrators login, so we need to create another condition to filter the logins and get a target set that contains only the the 'BUILTIN\Administrators login. In the New Condition dialog box, in the Name box, type “BUILTIN\Administrators Login”. Select the Login facet. In the Expression area, in the Field box, select @Name, in the Operator box select =, and in the Value box, type in “BUILTIN\Administrators”. This creates a condition to evaluate only the BUILTIN\Administrators login.

3.  Right click Policies, and select New Policy. In the New Policy dialog box, in the Name box, type “Local Admin Has No Server Access”. Check the Enabled box to enable the automated execution modes. In the Check condition box, select the “No server access” condition under Login. In the Against targets box, select the “BUILTIN\Administrators Login” condition as a filter for the login targets. In the Execution Mode box, select “On Change – Prevent” as the execution mode. This policy will stop any intentions to grant access to the local administrators group.

To see the policy in action, let’s try to add the BUILTIN\Administrators group back to the SQL Server. Run this command on the SQL Server

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS

We get an error message:

Policy 'Local Admin Has No Server Access' has been violated by '/Server/(local)/Login/BUILTIN\Administrators'.
This transaction will be rolled back.
Policy description: ''
Additional help: '' : ''.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50
The transaction ended in the trigger. The batch has been aborted.

As we can see above, the transaction has been rolled back and the login was not added due to the violation of the “Local Admin Has No Server Access” policy.

The execution mode of each policy is determined by the characteristics of the Management facet that is used by the condition in the policy. You will discover many of the facets do not allow the “On Change – Prevent” mode. How do we use the policy-based management to prevent incompliance in those cases? In the next article, I will show you how to use SQL Server Agent alerts and jobs to implement the automation. I will also show you how to evaluate policies against multiple servers by configuration servers and server groups.

Conclusion

We have introduced above the basics of Policy-based management and showed you an example of using this new feature to prevent incompliance.