Build a Policy-Based Management System for SQL Server 2008

Policy-based management is a new feature in SQL Server 2008 that lets you set the criteria for the “behavior” of various SQL Server objects. It also provides a mechanism to enforce policy. Policies can be created and enabled on the server, giving the DBA more control. This is very important in the context of creating common security practices in a company. I’d like to describe the steps that I took to create a flexible, policy-based mechanism for the validation of various security requirements and for enforcing the policies if a violation was found.

Talking Policy Management

Policy-based management in SQL Server 2008 is implemented as a set of rules set by the DBA for validating whether target objects (e.g., servers, databases, tables) comply with a specific policy. Verifiable properties of the targets are exposed through predefined objects—facets—which users can’t modify.

The state of the facet’s property is verified through a Boolean expression and is called a condition, which can be constructed by the user. A condition specifies the allowed state of a facet. Multiple properties of the same facet can be evaluated in one condition using the Boolean operators AND, OR. Each policy can have only one condition that checks the behavior of the particular targets.

All policies can be executed in On demand mode and On schedule mode. Some policies support the On change: log only mode. Very few policies can be regulated by the On change: prevent mode.

I assume that the reader is familiar with the basic design of policy-based management in SQL Server 2008 and its main components: policies, conditions, and facets. For specific information, see the related SQL Server Books Online (BOL) article"Administering Servers by Using Policy-Based Management."

The Challenge

One of my corporate clients in the financial industry (“the Company”) asked me to help develop a policy-based management system that would govern all security requirements for new and existing installations of SQL Server 2008. At the Company, the Windows engineering department is responsible for providing scripts for common, unattended SQL Server 2008 installations in each business division. This department wants to unify security criteria for all 2008 servers across the company, independent of environment, application, and support model.

They gave me a list of generic security requirements that I was supposed to convert into policies. Most of the requirements were based on Microsoft best practices; some were company-specific. All policies needed to be flexible enough to allow the DBA to enter exceptions if needed, without policy modification. SQL Server 2008 comes with a set of built-in policies.

Unfortunately, the current, out-of-the-box implementation of policy-based management in SQL Server 2008 has a few limitations:

Policies aren’t flexible enough. It’s difficult to create a generic policy common to each individual server that a DBA supports.

Only a few policies allow On change: prevent mode, and the DBA doesn’t have a policy enforcement mechanism.

Only the simplest rules are implemented in built-in policies.

Solution Description

I addressed the limitations of the out-of-the-box implementation by creating a table-driven solution that lets a DBA insert policy exceptions and regulate policy execution. As part of my solution, I built a mechanism of policy enforcement through a scheduled job that evaluates the policy and, if needed and requested, enforces it immediately.

After consultation with the client, I created a schedule called Verify_Policies_Schedule. All created policies were associated with the On schedule evaluation mode and this particular schedule. When at least one policy is scheduled to execute, SQL Server generates a job. I modified this system-created job by adding flexibility and an additional step that enforces policy if a violation is discovered.

I created in msdb four new SQL Server tables to store policy configuration, desired execution mode, and policy evaluation results.

dbo.PolicyConfiguration table. Exceptions to regular policy conditions can be entered in the dbo.PolicyConfiguration table. (See Listing 1, which creates this table.)

To add an exception to the policy for a particular server, database, or object, the DBA just enters the records into this table. Columns in this table store the following information: • PolicyConfigurationID—primary key • EvalPolicy—policy name • Target—name of the object (database, server) that should be included or excluded from the policy • IncludeFlag—1 (object included); 2 (object excluded)

For example, if you want to make an exception to the policy “Blank Password For SQL Logins” on ServerA, insert the following record into dbo.PolicyConfiguration:

Each policy can be evaluated in one of two modes: Mode Value 0 stands for Display Only mode—it only evaluates the policy, and no policy enforcement occurs if a violation is found. Mode Value 1 stands for Enforce Policy mode and enforces the policy if a violation is found.

dbo.PolicyExecution table. The evaluation mode for policy execution can be set individually in the dbo.PolicyExecution table, which Listing 2 creates. In this table, columns store the following information:

dbo.PolicyEvaluation table and dbo.PolicyEvaluation_FailureDetails table. When policies are executed on SQL Server, the results are accumulated in two system tables located in the msdb database: dbo.syspolicy_policy_execution_history and dbo.syspolicy_policy_execution_history_details. The job I created extracts the results of the most recent scheduled policy evaluations from dbo.syspolicy_policy_execution_history and stores them in a new table called msdb.dbo.PolicyEvaluation.

Violations of the most recent policy evaluations are extracted from the dbo.syspolicy_policy_execution_history_details system table and stored in the table called msdb.dbo.PolicyEvaluation_FailureDetails. Web Listing 1 creates these two tables.

Table 1 shows the column names and descriptions for table dbo.PolicyEvaluation and the dbo.PolicyEvaluation_FailureDetails.

For brevity’s sake, in the Table column, a 1 corresponds to the dbo.PolicyEvaluation table and a 2 corresponds to the dbo.PolicyEvaluation_FailureDetails table.

Creating a Policy

To illustrate the technique I used to create policy, let’s look at how I built the policy “Database DDL Triggers Enabled.” The Company has a trigger-based process that collects information about each user login to each database, except tempdb.

I was asked to create a policy for checking whether all mandatory DDL triggers were enabled in each database on each SQL Server 2005 or later instance. To do so, follow these steps:

Decide on a policy name. We need to know the policy name to enter policy exceptions (if any) in the msdb.dbo.PolicyConfiguration table.

Decide on server restrictions. As DDL triggers were introduced in the SQL Server 2005 release, we need to include this filter. Figure 1 shows the condition that verifies the above-mentioned criteria.

Decide on database restrictions. I created the condition “No tempdb” based on the Database facet that includes all user databases and three remaining system databases. This condition, which you can see in Figure 2, also makes sure that the database status is normal.

Create a condition to validate presence of disabled triggers. Any of the facets that allow checking conditions against database objects could be chosen here, such as Database or Database Security. In Listing 3, you can see an expression that shows how many user-created database DDL triggers are enabled in the database.

I used this expression to build the condition “Required Database DDL Triggers Enabled” for the policy, which Figure 3 shows. Note that the pane in the screenshot reveals just the beginning of the statement.

Create the policy. You create the policy “Database DDL Triggers Enabled” by specifying Check condition, target, server restrictions, and evaluation mode (On demand, for now). You also have the option to enter the description and assigned policy category in the Description tab, which Figure 4 shows.

Script the policy. Script as many of the settings as possible to perform the action again as needed. In my case, running the script that installs all policies becomes part of the SQL Server installation process on each new box. Microsoft provides the ability to script both policies and conditions, but there are a few problems with its built-in tool:

The scripting policy doesn’t provide a script of underlying conditions in the same output file, so you need to combine in the final script the output for all three conditions and the policy itself.

The generated “drop policy” script doesn’t notice underlying conditions referenced in other policies. This statement applies to conditions used as targets or server restrictions.

Despite these issues, the Microsoft scripting tool is useful. Without it, you’d find it hard to write all the commands that create necessary objects in the correct format.

Enforcing a Policy

Now let’s look at how all policies are enforced. Briefly, we run on schedule a job consisting of two steps: Step one validates each enabled policy on the server. Step two enforces policy violations for each configured policy by executing a stored procedure with multiple CASE statements inside for each policy.

By design, every time a policy is evaluated either on demand or on schedule, SQL Server saves the summary results of the evaluation in a system table, msdb.dbo.syspolicy_policy_execution_history. Additionally, policy failures against a particular target are saved in another system table, msdb.dbo.syspolicy_policy_execution_history_details.

We can analyze policy failures one record at a time and apply actions to fix them. For these purposes, I created a stored procedure called dbo.ApplyPolicies in msdb. This procedure has one parameter:

@StartTime datetime

which defines the beginning of the time slot in msdb.dbo.syspolicy_policy_execution_history that keeps the most recent policy evaluation records. This table stores all undeleted results (as many times as we run) for all policy evaluations.

As I wanted only the most recent ones, I moved the records (the most recent policy evaluation results since @StartTime) into two tables that I created earlier: msdb.dbo.PolicyEvaluation and msdb.dbo.PolicyEvaluation_FailureDetails.

Web Listing 2 shows the script of the dbo.ApplyPolicies stored procedure. To save space, only code associated with fixing violations of the policy “Database DDL Triggers Enabled” is shown.

Notes to Web Listing 2 – Stored Procedure dbo.ApplyFixes.

The body of the stored procedure looks like a giant loop. We step through each record in msdb.dbo.PolicyEvaluation_FailureDetails and then find the corresponding fix based on policy name by checking the whole bunch of IF statements:

IF @EvalPolicy = 'Database DDL Triggers Enabled' ……………do something

Code that enforces each individual policy looks similar to the one presented for “Database DDL Triggers Enabled” policy:

I parse the target column to extract the value of object name (in this case, it is a database name) which failed the policy. The full value of the target column in this case has the following format:

I create a temporary table #triggers with a list of disabled user-created triggers, not specified as an exception. The query I used here is similar to the one I used to define Check condition “Required Database DDL Triggers Enabled” (see: SELECT name FROM sys.triggers WHERE is_disabled = 1 AND is_ms_shipped = 0 and parent_class_desc = 'DATABASE' and name IN (SELECT Target FROM msdb.dbo.PolicyConfiguration WHERE EvalPolicy = 'Database DDL Triggers Enabled' AND IncludeFlag = 1)

I populate the name of the disabled trigger in the Result column of the msdb.dbo.PolicyEvaluation_FailureDetails table by updating the default value that was imported from the system table msdb.dbo.syspolicy_policy_execution_history_details. Originally this column had a number of disabled triggers in the current database. If we have more than one affected disabled trigger, we insert additional rows into our msdb.dbo.PolicyEvaluation_FailureDetails table with the name(s) of each additional disabled trigger.

The following fixes will be applied only if the evaluation mode for this policy is set to ‘Enforce Policy’

Then, in the internal loop, for each disabled trigger (not listed as exception) we change its status to “enabled” by constructing dynamic SQL and executing it. Results of execution are inserted into the corresponding row in the msdb.dbo.PolicyEvaluation_FailureDetails table.

I drop the temporary table #triggers

After completing the external loop for each policy failure, I update the status of the column FixFlag in our msdb.dbo.PolicyEvaluation table. Its value becomes 1 if a fix was successfully applied to each affected trigger. It will remain a 0 if for at least one trigger a change of status failed.

Web Listing 4 shows the script that creates the Verify_Policies job. There are a few problems with this script:

It isn’t flexible—the same Verify_Policies job must be executed on each SQL Server 2008 instance. In the presented variant, when the engineering team configured a new instance of SQL Server, they had to add flexibility to search the proper local Policy store.

The schedule UID for Verify_Policies_Schedule is hardcoded for now (it references Verify_Policies_Schedule), but it will be different every time a DBA installs the scripts on another server. We need to add flexibility by evaluating the policies associated with the schedule name (Verify_Policies_Schedule), not the system-generated UID.

There’s no problem in dynamically re-defining the content of the T-SQL step inside the job. Unfortunately, the job step, which is based on a PowerShell command, must be evaluated before the first job step starts.

I needed another job that would properly reconfigure Step 1 of the Verify_Policies job, then call this job with new, properly defined parameters. Web Listing 5 shows the script that creates another Configure_Verify_Policies job.

This job prepares the correct content of the steps in the following Verify_Policies job and immediately starts that job. Additionally, we want to make sure that at the time when we start the Configure_Verify_Policies job, no other instances of Verify_Policies jobs are running. Otherwise, we might not be able to uniquely identify the results of the last policy evaluation in the system tables.

However, instead of creating a new job, I decided to use the already existing dummy job. The first time a user sets the evaluation mode of any policy to On schedule, SQL Server by design creates this dummy job with a name starting with syspolicy_check_schedule_.

At the same time, you can’t delete this job unless there’s at least one scheduled policy in the system. So instead of keeping a useless job in the system, I renamed it Configure_Verify_Policies.

I deleted all the steps originally generated by SQL Server and dynamically redefined all the steps in this job. The Configure_Verify_Policies job consists of three steps, which you can also see in Web Listing 5:

Kill running job—In this step I define and execute dynamic T-SQL that checks if there’s a running instance of the Verify_Policies job and, if needed, kills the job.

Configure ScheduleUid—In this step, I do five things:

Find the UID for Verify_Policies_Schedule

Find the actual server and instance name

Finish building the dynamic SQL for the @command parameter of the msdb.dbo.sp_update_jobstep stored procedure and execute it; this changes the content of the PowerShell script needed to execute in step 1 of the Verify_Policies job.

Define the location of the job’s log file. The Company requested that I provide a log file for the DBA to track the steps of the execution of the ApplyFix stored procedure for debugging purposes. We decided to put the job’s log file in the same folder as the SQL Server Agent’s log.

Using the same technique as above, we then build and execute the dynamic SQL to assign output from Step 2 in the Verify_Policies job to log the file in the proper location.

Run the Verify_Policies job—In this step, we actually start the “updated” Verify_Policies job by invoking the sp_start_job stored procedure. At the end of the script, I associated the Configure_Verify_Policies job with Verify_Policies_Schedule.

Purging the Results of a Policy Evaluation

As I mentioned above, the results of each policy evaluation (scheduled or running on-demand from SQL Server Management Studio—SSMS) are stored in system tables. SQL Server 2008 installs the preconfigured system job syspolicy_purge_history that’s supposed to clean those tables.

This job must run periodically to remove aged data from the msdb.dbo.syspolicy_policy_execution_history table and the msdb.dbo.syspolicy_policy_execution_history_details table. Upon installation, the job is configured to keep all data. The code in Listing 5 would run to enable syspolicy_purge_history job and purge the policy evaluation results after 15 days of storage.

You can use SSMS to change the default schedule for this job and the length of the storage interval, which Figure 5 shows. To change history retention, go to Management, Policy Management, and right-click Properties, then change the value of the HistoryRetentionInDays parameter.

Solution Management

The simplest way to include (or exclude) policy from evaluation is enabling (or disabling) it. Use SSMS to exclude a policy from the validation process. Go to Management, Policy Management, Policies, and select Policy, then right-click Enable (or Disable, as the case may be).

To add exceptions to the policy validation process, you need to add at least one record to the dbo.PolicyConfiguration table and use a value of 2 for the IncludeFlag column. In some cases you can specify additional elements to include in the policy validation process.

These inclusions should have a value of 1 in the IncludeFlag column. For example, to include a database DDL trigger in the validation process, use the following command:

As I discussed earlier, policies can run either in Display Only or Enforce Policy modes. To change the evaluation mode for a policy, set the corresponding value of the column EvaluationMode in the table msdb.dbo.PolicyExecution. For example, to run the policy “Database DDL Triggers Enabled” in Display Only mode, use the following code:

select * from msdb.dbo.PolicyEvaluation order by EvalPolicy select * from msdb.dbo.PolicyEvaluation _FailureDetails order by EvalPolicy, Target

The previous results of policy execution in those two tables are truncated at the beginning of each run of the Configure_Verify_Policies job. Both tables refresh with the most recent results of policy evaluation.

Successfully run policies will have a value of 1 in the SuccessFlag column in the msdb.dbo.PolicyEvaluation table. Policy violations are listed in the msdb.dbo.PolicyEvaluation_FailureDetails table.

If a policy runs in the EnforcePolicy mode, the results of policy enforcement will be in the FixFlag column. Positive values in this column mean a successful fix of a policy violation.

A log file is generated every time a Configure_Verify_Policies job runs. The Log file Verify_Policies.txt is located in the same folder as SQL Server Agent logs (for example, C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\Log).

You must have proper OS permissions to view the log file. Additionally, you can build a notification process if a process finds policy violations by analyzing the value of the SuccessFlag column in the msdb.dbo.PolicyEvaluation table.

Solving the Limitations of SQL Server Policy-Based Management

This solution provided DBAs with a tool that simultaneously enforced common policies across all managed SQL Server instances and allowed DBAs to enter exceptions if an application had special requirements. Last, but not least, the engineering team acquired the methodology to expand existing sets of policies.

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More