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.

These policies aren’t installed by default, but they can be easily imported to the server. For details, see the BOL article "How to: Export and Import a Policy-Based Management Policy."

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.)

                              USE msdb                                SET NOCOUNT ON                                GO                                IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyConfiguration')                                  DROP TABLE \[dbo\].\[PolicyConfiguration\]                                GO                                CREATE TABLE \[dbo\].\[PolicyConfiguration\](                                 \[PolicyConfigurationID\] \[bigint\] NOT NULL IDENTITY(1,1),                                        \[EvalPolicy\] \[varchar\](500) NOT NULL,                                         \[Target\] \[varchar\](400) NOT NULL,                                        \[IncludeFlag\] \[int\] NULL,     --Include = 1, Exclude = 2                                        CONSTRAINT PK_PolicyConfiguration PRIMARY KEY (PolicyConfigurationID),                                        CONSTRAINT UQ_PolicyConfiguration UNIQUE (EvalPolicy, Target)                                ) ON \[PRIMARY\]                                 IF @@ERROR = 0                                  PRINT 'TABLE PolicyConfiguration IN msdb CREATED SUCCESSFULLY'                                ELSE                                PRINT 'COULD NOT CREATE TABLE PolicyConfiguration IN msdb'                                GO                               

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:

                              INSERT dbo.PolicyConfiguration (EvalPolicy,                                   Target, IncludeFlag)                                 VALUES ('Blank Password For SQL Logins',                                   'ServerA', 2)                              

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:

  • PolicyExecutionID—primary key
  • EvalPolicy—policy name
  • EvaluationMode—0 (display only); 1 (enforce policy)
                              USE msdb                                SET NOCOUNT ON                                GO                                   IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyExecution')  	                                DROP TABLE \[dbo\].\[PolicyExecution\]                                GO                                CREATE TABLE \[dbo\].\[PolicyExecution\](                                        \[PolicyExecutionID\] \[bigint\] NOT NULL IDENTITY(1,1),                                       \[EvalPolicy\] \[varchar\](500) NOT NULL,                                        \[EvaluationMode\] \[int\] NOT NULL DEFAULT(0),                                       CONSTRAINT PK_PolicyExecution PRIMARY KEY (PolicyExecutionID),                                       CONSTRAINT UQ_PolicyExecution UNIQUE (EvalPolicy),                                       CHECK (EvaluationMode >= 0 and EvaluationMode <= 1)                                ) ON \[PRIMARY\]                                 IF @@ERROR = 0  	                                PRINT	'TABLE PolicyExecution IN msdb CREATED SUCCESSFULLY'                                ELSE  	                              PRINT	'COULD NOT CREATE TABLE PolicyExecution IN msdb'                                GO                                

For example, to see whether the policy “Blank Password For SQL Logins” was violated without enforcing password assignment, insert the following record into dbo.PolicyExecution:

                              INSERT dbo.PolicyExecution (EvalPolicy,                                   EvaluationMode) VALUES ('Blank Password                                   For SQL Logins', 0)                              

To immediately enforce the policy by assigning some default password, insert the following row into dbo.PolicyExecution:

                              INSERT dbo.PolicyExecution (EvalPolicy,                                   EvaluationMode)                                 VALUES ('Blank Password                                   For SQL Logins', 1)                              

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.

                              USE msdb                              GO                              IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation')                                   DROP TABLE \[dbo\].\[PolicyEvaluation\]                              GO                              CREATE TABLE \[dbo\].\[PolicyEvaluation\](                                \[rec_id\] \[int\] NOT NULL,                                \[history_id\] \[bigint\] NOT NULL,                                \[policy_id\] \[int\] NOT NULL,                                \[EvalPolicy\] \[varchar\](500) NOT NULL,                                \[EvalDateTime\] \[datetime\] NULL,                                \[SuccessFlag\] \[int\] NULL,                                \[FixFlag\] \[int\] NULL,                                \[ErrorMsg\] \[nvarchar\](max) NULL,                                CONSTRAINT PK_PolicyEvaluation PRIMARY KEY (rec_id)                              ) ON \[PRIMARY\]                              IF @@ERROR = 0                                  PRINT 'TABLE PolicyEvaluation IN msdb CREATED SUCCESSFULLY'                              ELSE                                  PRINT 'COULD NOT CREATE TABLE PolicyEvaluation IN msdb'                              GO                              IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation_FailureDetails')                                  DROP TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\]                              GO                              CREATE TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\](                                \[failure_id\] \[int\] NOT NULL,                                \[EvalPolicy\] \[varchar\](500) NOT NULL,                                \[Target\] \[varchar\](400) NOT NULL,                                \[EvalDateTime\] \[datetime\] NULL,                                \[EvalResults\] \[xml\] NULL,                                \[FixFlag\] \[int\] NULL,                                \[FixErrorMsg\] \[nvarchar\](max) NULL,                                CONSTRAINT PK_PolicyEvaluation_FailureDetails PRIMARY KEY (failure_id)                              ) ON \[PRIMARY\]                              IF @@ERROR = 0                                  PRINT 'TABLE PolicyEvaluation_FailureDetails IN msdb CREATED SUCCESSFULLY'                              ELSE                                  PRINT 'COULD NOT CREATE TABLE PolicyEvaluation_FailureDetails IN msdb'                              GO                              

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:

  1. Decide on a policy name. We need to know the policy name to enter policy exceptions (if any) in the msdb.dbo.PolicyConfiguration table.
  2. 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.
  3. 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.
  4. 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.
                                  SELECT COUNT(*)                                 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)                               

    Then we use the ExecuteSQL function, which allows embedding of a SELECT statement in a Policy-Based Management expression, which Listing 4 shows.

                                  SELECT Statement                                   ExecuteSql ('Numeric', 'SELECT COUNT(*)                                 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 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.
  5. 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.
  6. 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:
  • SQLSERVER:\SQL\YourServerName\YourInstanceName\Databases\YourDatabaseName
  • 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.
                              USE msdb                              GO                              IF OBJECT_ID ( 'dbo.ApplyPolicies', 'P' ) IS NOT NULL                              DROP PROCEDURE dbo.ApplyPolicies                              GO                              Create Procedure dbo.ApplyPolicies                              (                              @StartTime    datetime                              ,@ApplyFix    int    = 1                              )                              AS                              /*                              If @ApplyFix = 1, SP will try to apply policy fix                              If @ApplyFix = 0, SP will not try to apply policy fix                              */                              SET NOCOUNT ON;                              DECLARE       @failure_id          int                              ,@rc                 int                              ,@EvalPolicy  varchar(500)                              ,@Target             nvarchar(400)                              ,@EvalResults xml                              ,@EvalDateTime       datetime                              ,@NoAction           varchar(100)                              ,@NeedRestart varchar(100)                              ,@Applied            varchar(100)                              ,@Disabled           varchar(100)                              ,@temp               nvarchar(max)                              ,@temp2                    nvarchar(max)                              ,@DB_Name            nvarchar(128)                              ,@obj_name           nvarchar(300)                              ,@Login                    nvarchar(128)                              ,@type               varchar(5)                              ,@Permission  nvarchar(128)                              ,@ParmDef            nvarchar(500)                              ,@rc2                int                              ,@i2                 int                              ,@failure_id2 int                              ,@EvaluationMode int                              BEGIN TRY                              SELECT @NoAction            = 'No Action Taken'                              ,@Applied            = 'Policy Applied'                              ,@NeedRestart = 'Change Applied. The setting takes effect                                after the server is restarted'                              ,@Disabled           = 'Login Disabled'                              TRUNCATE TABLE dbo.PolicyEvaluation_FailureDetails;                              TRUNCATE TABLE dbo.PolicyEvaluation;                              PRINT  CONVERT(varchar(30), getdate(),121) + '  Truncated                                PolicyEvaluation Tables'                              IF EXISTS(SELECT 1 FROM dbo.syspolicy_policy_execution_history WHERE                                start_date >= @StartTime)                              BEGIN                              WITH LatestEval AS                              (                              SELECT                              history_id,                              policy_id,                              rn = ROW_NUMBER() OVER (PARTITION BY policy_id ORDER BY start_date Desc)                              FROM                              dbo.syspolicy_policy_execution_history                              WHERE                              start_date >= @StartTime   --'10/17/2008'                              )                              INSERT INTO dbo.PolicyEvaluation                              (\[rec_id\]                              ,\[history_id\]                              ,\[policy_id\]                              ,\[EvalPolicy\]                              ,\[EvalDateTime\]                              ,\[SuccessFlag\]                              ,\[FixFlag\]                              ,\[ErrorMsg\])                              SELECT                              \[rec_id\]             = ROW_NUMBER() OVER (ORDER BY p.name Asc),                              \[history_id\]  = s.history_id,                              \[policy_id\]          = s.policy_id,                              \[EvalPolicy\]  = p.name,                              \[EvalDateTime\]       = s.start_date,                              \[SuccessFlag\] = s.result,                              \[FixFlag\]            = NULL,                              \[ErrorMsg\]           = s.exception                              FROM                              LatestEval    l                              INNER JOIN dbo.syspolicy_policy_execution_history s                              ON l.history_id = s.history_id                              INNER JOIN dbo.syspolicy_policies p                              ON s.policy_id = p.policy_id                              WHERE                              l.rn = 1                              PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy                                Evaluation Table'                              IF EXISTS (SELECT 1 FROM \[dbo\].\[PolicyEvaluation\] WHERE \[SuccessFlag\] = 0)                              BEGIN                              INSERT INTO \[dbo\].\[PolicyEvaluation_FailureDetails\]                              (\[failure_id\]                              ,\[EvalPolicy\]                              ,\[Target\]                              ,\[EvalDateTime\]                              ,\[EvalResults\]                              ,\[FixFlag\]                              ,\[FixErrorMsg\])                              SELECT                              \[failure_id\]  = ROW_NUMBER() OVER (ORDER BY p.EvalPolicy,d.detail_id Asc)                              ,\[EvalPolicy\] = p.EvalPolicy                              ,\[Target\]            = d.target_query_expression                              ,\[EvalDateTime\]      = d.execution_date                              ,\[EvalResults\]       = d.result_detail                              ,\[FixFlag\]           = NULL                              ,\[FixErrorMsg\]       = NULL                              FROM                              dbo.PolicyEvaluation p                              INNER JOIN dbo.syspolicy_policy_execution_history_details d                              ON p.history_id = d.history_id                              WHERE                              p.SuccessFlag = 0                              SELECT @failure_id   = 1, @rc = @@RowCount                              PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy                                Evaluation_FailureDetails Table'                              IF @ApplyFix = 1                              BEGIN                              PRINT  ' '                              PRINT  CONVERT(varchar(30), getdate(),121) + '  Start Processing                                Policy Failures'                              WHILE (@failure_id <= @rc)                              BEGIN                              BEGIN TRY                              SELECT        @EvalPolicy          = EvalPolicy                              ,@Target             = Target                              ,@EvalResults = EvalResults                              ,@EvalDateTime       = EvalDateTime                              FROM                              dbo.PolicyEvaluation_FailureDetails                              WHERE                              failure_id    = @failure_id                              SELECT @EvaluationMode                   = EvaluationMode                              FROM   msdb.dbo.PolicyExecution                              WHERE  EvalPolicy                        = @EvalPolicy                              -----------------------------------------------------------------------                              IF @EvalPolicy = 'Server DDL Triggers Enabled'                              here is the code that fixes this policy                              -----------------------------------------------------------------------                              IF @EvalPolicy = 'Database DDL Triggers Enabled'                              BEGIN                              --SQLSERVER:\SQL\GARYZAIK5\SQL2K8\Databases\Finance                              SET @DB_Name  = RIGHT(@Target,CHARINDEX('\',REVERSE(@Target))-1)                              IF OBJECT_ID('tempdb..#triggers') IS NOT NULL                              DROP TABLE #triggers                              CREATE TABLE #triggers (                              rn                   int NOT NULL IDENTITY(1,1),                              obj_name             nVARCHAR(200),                              state_desc    nVARCHAR(10),                              Result        xml NULL                              )                              SELECT @temp = 'USE ' + @DB_Name + '                              INSERT #triggers (obj_name, state_desc)                              SELECT                              obj_name      = name,                              state_desc    = CASE WHEN is_disabled = 1                                THEN ''DISABLED'' ELSE ''ENABLED'' End                              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)                              '                              EXEC (@temp) ;                              SELECT @rc2 = (SELECT COUNT(*) FROM #triggers)                              SET           @i2 = 1 ;                              WITH temp AS (                              SELECT rn,                              res = (SELECT * FROM #triggers w WHERE w.rn = t.rn for xml auto )                              FROM #triggers t)                              UPDATE t                              SET           Result = e.res                              FROM   #triggers t,                              temp e                              WHERE  t.rn= e.rn                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           Target        = @DB_Name + ': ' + l.obj_name,                              EvalResults   = l.Result                              FROM   #triggers     l                              WHERE  failure_id    = @failure_id                              AND           l.rn          = 1                              SELECT @failure_id2 = ISNULL((SELECT MAX(failure_id)                              FROM msdb.dbo.PolicyEvaluation_FailureDetails),0)                              INSERT msdb.dbo.PolicyEvaluation_FailureDetails (failure_id,EvalPolicy,                                Target,EvalDateTime,EvalResults)                              SELECT failure_id    = @failure_id2 + rn - 1,                              EvalPolicy    = @EvalPolicy,                              Target        = @DB_Name + ': ' + l.obj_name,                              EvalDateTime= @EvalDateTime,                              EvalResults   = l.Result                              FROM   #triggers l                              WHERE  rn                   > 1                              WHILE (@i2 <= @rc2)                              BEGIN                              SELECT @obj_name = obj_name FROM #triggers WHERE rn = @i2                              IF @EvaluationMode = 0                              BEGIN                              IF @i2 > 1                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           FixFlag              = 0,                              FixErrorMsg   = @NoAction                              WHERE  failure_id    = @failure_id2 + @i2 - 1                              ELSE                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           FixFlag              = 0,                              FixErrorMsg   = @NoAction                              WHERE  failure_id    = @failure_id                              END                              ELSE                              BEGIN                              SET @temp            = N'USE ' + @DB_Name + ';                              ENABLE Trigger ' +  @obj_name + N' ON DATABASE'                              SET @ParmDef  = N'@DB_Name nvarchar(128), @obj_name nvarchar(300)'                              EXEC sp_executesql @temp, @ParmDef, @DB_Name, @obj_name                              IF @i2 > 1                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           FixFlag              = 1,                              FixErrorMsg   = @Applied                              WHERE  failure_id    = @failure_id2 + @i2 - 1                              ELSE                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           FixFlag              = 1,                              FixErrorMsg   = @Applied                              WHERE  failure_id    = @failure_id                              END                              SET @i2 = @i2 + 1                              END                              DROP TABLE #triggers                              END                              -------------------------------------------------------------                              END TRY                              BEGIN CATCH                              DECLARE @ErrorNumber int,                              @ErrorSeverity       int,                              @ErrorState          int,                              @ErrorProcedure      sysname,                              @ErrorLine           int,                              @ErrorMessage nvarchar(max)                              SELECT                              @ErrorNumber = ERROR_NUMBER()                              ,@ErrorSeverity = ERROR_SEVERITY()                              ,@ErrorState = ERROR_STATE()                              ,@ErrorProcedure = ERROR_PROCEDURE()                              ,@ErrorLine = ERROR_LINE()                              ,@ErrorMessage = ERROR_MESSAGE();                              PRINT 'Error ' + CONVERT(varchar(50), @ErrorNumber) +                              ', Severity ' + CONVERT(varchar(5), @ErrorSeverity) +                              ', State ' + CONVERT(varchar(5), @ErrorState) +                              ', Procedure ' + ISNULL(@ErrorProcedure, '-') +                              ', Line ' + CONVERT(varchar(5), @ErrorLine);                              PRINT @ErrorMessage;                              UPDATE dbo.PolicyEvaluation_FailureDetails                              SET           FixFlag              = 0,                              FixErrorMsg   = @ErrorMessage                              WHERE  failure_id    = @failure_id                              END CATCH                              SET @failure_id = @failure_id + 1                              END ;                              WITH FixResults AS (                              SELECT EvalPolicy,                              FixFlag       = MIN(FixFlag)                              FROM   dbo.PolicyEvaluation_FailureDetails                              GROUP BY                              EvalPolicy                              )                              UPDATE dbo.PolicyEvaluation                              SET           FixFlag              = f.FixFlag                              FROM   FixResults    f                              WHERE  PolicyEvaluation.EvalPolicy = f.EvalPolicy                              END                              END                              END                              PRINT  CONVERT(varchar(30), getdate(),121) + '                               *** The End ***'                              ----------------------------------------------------------                              END TRY                              BEGIN CATCH                              PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +                              ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +                              ', State ' + CONVERT(varchar(5), ERROR_STATE()) +                              ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +                              ', Line ' + CONVERT(varchar(5), ERROR_LINE());                              PRINT ERROR_MESSAGE();                              END CATCH                              GO                              

The Company wanted to run all policy evaluations at the same time: every Sunday. So I created Verify_Policies_Schedule (see Web Listing 3) and associated it with all created policies.

                              PRINT  'CREATING SCHEDULE FOR POLICY EVALUATION'                              BEGIN TRANSACTION                              BEGIN TRY                                     Declare       @ReturnCode int,                                                         @schedule_uid uniqueidentifier,                                                         @schedule_id int,                                                         @pol_id int                                     SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules                                _localserver_view WHERE name = N'Verify_Policies_Schedule'                                     IF  @schedule_uid IS NULL                                     BEGIN                                            SELECT @ReturnCode = 0                                            EXEC @ReturnCode =  msdb.dbo.sp_add_schedule                                                   @schedule_name                    = N'Verify_Policies                                _Schedule'                                                   ,@enabled                         = 1                                                   ,@freq_type                       = 8    --weekly                                                   ,@freq_interval                   = 1    --Sunday                                                   ,@freq_subday_type         = 1    --at the specific time                                                   ,@freq_subday_interval            = 0                                                             ,@freq_relative_interval   = 0                                                   ,@freq_recurrence_factor   = 1    --once a week                                                   ,@active_start_date        = 20080101                                                   ,@active_end_date          = 99991231                                                   ,@active_start_time        = 500         --00:05:00                                                   ,@active_end_time          = 235959                                                   ,@owner_login_name         = 'sa'                                                   ,@schedule_uid                    = @schedule_uid OUTPUT                                                   ,@schedule_id              = @schedule_id OUTPUT                                                                                      IF (@ReturnCode <> 0)                                            BEGIN                                                   PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION                                ROLLED BACK'                                                   GOTO QuitWithRollback                                            END                                     END                                        SELECT @pol_id = policy_id FROM msdb.dbo.syspolicy_policies WHERE                                name = N'Database DDL Triggers Enabled'                                     IF @pol_id IS NOT NULL                                            EXEC msdb.dbo.sp_syspolicy_update_policy                                                   @policy_id                        = @pol_id,                                                   @execution_mode                   = 4,                                                   @is_enabled                       = True,                                                   @schedule_uid              = @schedule_uid                              /* Here you would insert similar code for other policies */                                     COMMIT TRAN                                     PRINT  'SUCCESSFULLY CHANGED EVALUATION MODE FOR ALL POLICIES                                TO On Schedule'                                                                       END TRY                              BEGIN CATCH                                     PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'                                     PRINT  ''                                     SELECT                                      ERROR_NUMBER() AS ErrorNumber                                      ,ERROR_SEVERITY() AS ErrorSeverity                                      ,ERROR_STATE() AS ErrorState                                      ,ERROR_LINE() AS ErrorLine                                      ,ERROR_MESSAGE() AS ErrorMessage;                                  IF @@TRANCOUNT > 0                                      ROLLBACK TRANSACTION;                              END CATCH                                  QuitWithRollback:                                 GO                              

After changing the evaluation mode for all policies to On schedule and associating them with Verify_Policies_Schedule, I created Verify_Policies_Job. This job consists of two steps:

  1. Check the policy store on each server and evaluate all scheduled policies by running the Windows PowerShell command
                                  Invoke-PolicyEvaluation                              
    For a description of this command, see the Microsoft article "Using the Invoke-PolicyEvaluation cmdlet."
     
  2. Fix the problems (if possible) by executing the stored procedure dbo.ApplyFix, which Web Listing 1 shows.

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.
                              USE \[msdb\]                              GO                              BEGIN TRAN                              DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier, @schedule_id int                              SELECT @ReturnCode = 0                              IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Verify_Policies')                              BEGIN                               EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name=N'Verify_Policies',                                 @delete_unused_schedule=0                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              END                              IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'\[Uncategorized                                 (Local)\]' AND category_class=1)                              BEGIN                               EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',                                 @name=N'\[Uncategorized (Local)\]'                               IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              END                              DECLARE @jobId BINARY(16)                              EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Verify_Policies',                                 @enabled=1,                                 @notify_level_eventlog=0,                                 @notify_level_email=0,                                 @notify_level_netsend=0,                                 @notify_level_page=0,                                 @delete_level=0,                                 @description=N'No description available.',                                 @category_name=N'\[Uncategorized (Local)\]',                                 @owner_login_name=N'sa', @job_id = @jobId OUTPUT                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              EXEC @ReturnCode = msdb.dbo.sp_add_jobserver                                  @job_name = N'Verify_Policies',                                  @server_name = @@ServerName                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                /******   Step \[Evaluate policies.\]     ******/                              EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Evaluate                                 policies',                                 @step_id=1,                                 @cmdexec_success_code=0,                                 @on_success_action=4,  --go to step 2                                @on_success_step_id=2,                                 @on_fail_action=2,                                 @on_fail_step_id=0,                                 @retry_attempts=0,                                 @retry_interval=0,                                 @os_run_priority=0, @subsystem=N'PowerShell',                                 @command=N'dir SQLSERVER:\SQLPolicy\MyComputer\MyInstance\                                Policies | where \{ $_.ScheduleUid -eq "B1594BBB-269C-4BDB-9866-C0CD8A7AE694"                                  \} |  where \{ $_.Enabled -eq 1\} | where \{$_.AutomatedPolicyEvaluationMode                                 -eq 4\} | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName                                 MyComputer\MyInstance',                                 @flags=0                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              /******   Step \[Fix the Problems\]     ******/                              DECLARE @command2 nvarchar(max)                              SELECT @command2 = N'EXEC dbo.ApplyPolicies ''' + CONVERT(varchar(30),                                getdate(),120) + ''''                              EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fix                                 the Problems (if possible).',                                 @step_id=2,                                 @cmdexec_success_code=0,                                 @on_success_action=1,                                 @on_success_step_id=0,                                 @on_fail_action=2,                                 @on_fail_step_id=0,                                 @retry_attempts=0,                                 @retry_interval=0,                                 @os_run_priority=0, @subsystem=N'TSQL',                                 @command= @command2,                                 @database_name=N'msdb',                                 @flags=0                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                COMMIT TRANSACTION                              PRINT 'JOB Verify_Policies WAS CREATED SUCCESSFULLY'                              GOTO EndSave                              QuitWithRollback:                              IF (@@TRANCOUNT > 0)                               BEGIN                               ROLLBACK TRANSACTION                               PRINT 'COULD NOT CREATE JOB Verify_Policies'                              END                              EndSave:                              GO                              

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.

                              USE \[msdb\]                              GO                              BEGIN TRAN                              DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier,                                @schedule_id int, @dummy_job_id uniqueidentifier                              SELECT @ReturnCode = 0                              IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE                                name=N'\[Uncategorized (Local)\]' AND category_class=1)                              BEGIN                                     EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',                                @type=N'LOCAL', @name=N'\[Uncategorized (Local)\]'                                     IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              END                              DECLARE @jobId BINARY(16)                              SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules                                _localserver_view WHERE name = N'Verify_Policies_Schedule'                              SELECT @dummy_job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name                                = 'syspolicy_check_schedule_' + CONVERT(char(36),@schedule_uid)                              IF @dummy_job_id IS NOT NULL                              BEGIN                                     EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@dummy_job_id,                                                   @new_name = N'Configure_Verify_Policies',                                                   @enabled=1,                                                   @notify_level_eventlog=0,                                                   @notify_level_email=0,                                                   @notify_level_netsend=0,                                                   @notify_level_page=0,                                                   @delete_level=0,                                                   @description=N'No description available.',                                                   @category_name=N'\[Uncategorized (Local)\]',                                                   @owner_login_name=N'sa'                                                                                       SET @jobId=@dummy_job_id                              END                              ELSE                              BEGIN                                     SELECT @jobId = job_id FROM msdb.dbo.sysjobs_view WHERE                                name = N'Configure_Verify_Policies'                                     IF @jobId IS NOT NULL                                            EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@jobId,                                                         @enabled=1,                                                         @notify_level_eventlog=0,                                                         @notify_level_email=0,                                                         @notify_level_netsend=0,                                                         @notify_level_page=0,                                                         @delete_level=0,                                                         @description=N'No description available.',                                                         @category_name=N'\[Uncategorized (Local)\]',                                                         @owner_login_name=N'sa'                                     ELSE                                            EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=                                N'Configure_Verify_Policies',                                                   @enabled=1,                                                   @notify_level_eventlog=0,                                                   @notify_level_email=0,                                                   @notify_level_netsend=0,                                                   @notify_level_page=0,                                                   @delete_level=0,                                                   @description=N'No description available.',                                                   @category_name=N'\[Uncategorized (Local)\]',                                                   @owner_login_name=N'sa',                                                   @job_id = @jobId OUTPUT                                      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                              END                                                             IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers WHERE job_id = @jobId)                              BEGIN                                     EXEC @ReturnCode = msdb.dbo.sp_add_jobserver                                            @job_name = N'Configure_Verify_Policies',                                            @server_name = @@ServerName                                     IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                               END                                                             Declare @steps int, @i int                              select @steps = COUNT(*) from dbo.sysjobsteps s, dbo.sysjobs j                                where s.job_id = j.job_id and j.name = N'Configure_Verify_Policies'                              select @i = @steps                              WHILE (@i > 0)                              BEGIN                                      EXEC dbo.sp_delete_jobstep                                            @job_name = N'Configure_Verify_Policies',                                            @step_id = @i ;                                                                    SET @i = @i - 1                              END                              /******   Step \[Kill Running Job\]     ******/                              EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,                                @step_name=N'Kill Running Job',                                            @step_id=1,                                            @cmdexec_success_code=0,                                            @on_success_action=4,                                            @on_success_step_id=2,                                            @on_fail_action=2,                                            @on_fail_step_id=0,                                            @retry_attempts=0,                                            @retry_interval=0,                                            @os_run_priority=0, @subsystem=N'TSQL',                                            @command=N'                              IF EXISTS (SELECT j.\[name\]                                            from msdb.dbo.sysjobactivity a                                            join msdb.dbo.sysjobs j                                            on j.job_id=a.job_id and j.name = ''Verify_Policies''                                            where a.start_execution_date is not null                                            and a.job_history_id is null)                                     EXEC msdb.dbo.sp_stop_job N''Verify_Policies''                                            ',                                            @database_name=N'msdb',                                            @flags=0                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                                              /******   Step \[Configure ScheduleUid\]     ******/                              Declare @StepString  nvarchar(max)                              SET           @StepString = N'DECLARE    @TargetSrv    sysname,                                @ScheduleUid uniqueidentifier, @sScheduleUid varchar (40),                                @CommandString nvarchar(max)                              Declare       @DataLocation varchar(1000), @OutputFile varchar(1000)                              exec master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',                              N''Software\Microsoft\MSSQLServer\SQLServerAgent'', N''ErrorLogFile'',                                @DataLocation OUTPUT                              SELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX(''\'',REVERSE(@DataLocation)))                              SET           @OutputFile          = @DataLocation + ''\Verify_Policies.log''                                                             SELECT @TargetSrv = @@ServerName                              SELECT @ScheduleUid = schedule_uid FROM msdb.dbo.sysschedules                                _localserver_view WHERE name = ''Verify_Policies_Schedule''                              SELECT @sScheduleUid= CONVERT(nchar(36),@ScheduleUid)                              IF CHARINDEX(''\'',@TargetSrv) > 0                                     SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +                                @TargetSrv + ''\Policies | where \{ $_.ScheduleUid -eq "'' +                                @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where                                \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation                                -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv                              ELSE                                     SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +                                @TargetSrv + ''\DEFAULT\Policies | where \{ $_.ScheduleUid -eq "'' +                                @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where                                \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation                                -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv                                                             EXEC msdb.dbo.sp_update_jobstep                                     @job_name = ''Verify_Policies''                                     ,@step_id = 1                                     ,@command = @CommandString                                                                 SELECT @CommandString = N''EXEC dbo.ApplyPolicies '''''' + CONVERT                                (varchar(30),getdate(),120) + '''''',1''                              EXEC msdb.dbo.sp_update_jobstep                                     @job_name = ''Verify_Policies''                                     ,@step_id = 2                              ,@output_file_name=@OutputFile                                        ,@command = @CommandString        '                                                             EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=                                N'Configure ScheduleUid',                                            @step_id=2,                                            @cmdexec_success_code=0,                                            @on_success_action=4,                                            @on_success_step_id=3,                                            @on_fail_action=2,                                            @on_fail_step_id=0,                                            @retry_attempts=0,                                            @retry_interval=0,                                            @os_run_priority=0, @subsystem=N'TSQL',                                            @command=@StepString,                                            @database_name=N'msdb',                                            @flags=0                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                                              /******   Step \[Run Verify_Policies Job\]     ******/                              EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,                                            @step_name=N'Run Verify_Policies Job',                                            @step_id=3,                                            @cmdexec_success_code=0,                                            @on_success_action=1,                                            @on_success_step_id=0,                                            @on_fail_action=2,                                            @on_fail_step_id=0,                                            @retry_attempts=0,                                            @retry_interval=0,                                            @os_run_priority=0, @subsystem=N'TSQL',                                            @command=N'EXEC dbo.sp_start_job N''Verify_Policies'' ',                                            @database_name=N'msdb',                                            @flags=0                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                                              EXEC @ReturnCode = msdb.dbo.sp_attach_schedule                                 @job_name = N'Configure_Verify_Policies',                                 @schedule_name = N'Verify_Policies_Schedule' ;                              IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback                                                             COMMIT TRANSACTION                              PRINT  'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'                              GOTO EndSave                              QuitWithRollback:                              IF (@@TRANCOUNT > 0)                              BEGIN                                     ROLLBACK TRANSACTION                                     PRINT  'COULD NOT CREATE JOB Configure_Verify_Policies'                              END                              EndSave:                              GO                              

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:

  1. 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.
     
  2. 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.

                              BEGIN TRANSACTION                                DECLARE @ReturnCode INT                                EXEC msdb.dbo.sp_syspolicy_configure @name=                                    Enabled, @value=1                                EXEC msdb.dbo.sp_syspolicy_configure @name=                                    N'HistoryRetentionInDays', @value=15                                EXEC @ReturnCode =  msdb.dbo.sp_update_job                                     @job_name=N'syspolicy_purge_history',                                     @enabled=1                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO                                     QuitWithRollback                                COMMIT TRANSACTION                                GOTO EndSave                                QuitWithRollback:                                      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION                                EndSave:                              

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:

                              INSERT dbo.PolicyConfiguration (EvalPolicy,                                   Target, IncludeFlag)   	                              VALUES (‘Database DDL Triggers Enabled’,                                   ‘some_trigger_name’, 1)                              

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:

                              UPDATE msdb.dbo.PolicyExecution                                SET EvaluationMode = 0	                                WHERE EvalPolicy= ‘Database DDL                                   Triggers Enabled’                              

To view the results of policy execution, run the following commands:

                              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.