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.

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:

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:

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 msdbGOIF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation')     DROP TABLE [dbo].[PolicyEvaluation]GOCREATE 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'GOIF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation_FailureDetails')    DROP TABLE [dbo].[PolicyEvaluation_FailureDetails]GOCREATE 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:

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:

@StartTimedatetime

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:

USE msdbGOIF OBJECT_ID ( 'dbo.ApplyPolicies', 'P' ) IS NOT NULLDROP PROCEDURE dbo.ApplyPoliciesGOCreate Procedure dbo.ApplyPolicies(@StartTime    datetime,@ApplyFix    int    = 1)AS/*If @ApplyFix = 1, SP will try to apply policy fixIf @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 intBEGIN TRYSELECT @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)BEGINWITH LatestEval AS(SELECThistory_id,policy_id,rn = ROW_NUMBER() OVER (PARTITION BY policy_id ORDER BY start_date Desc)FROMdbo.syspolicy_policy_execution_historyWHEREstart_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.exceptionFROMLatestEval    lINNER JOIN dbo.syspolicy_policy_execution_history sON l.history_id = s.history_idINNER JOIN dbo.syspolicy_policies pON s.policy_id = p.policy_idWHEREl.rn = 1PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy  Evaluation Table'IF EXISTS (SELECT 1 FROM [dbo].[PolicyEvaluation] WHERE [SuccessFlag] = 0)BEGININSERT 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]       = NULLFROMdbo.PolicyEvaluation pINNER JOIN dbo.syspolicy_policy_execution_history_details dON p.history_id = d.history_idWHEREp.SuccessFlag = 0SELECT @failure_id   = 1, @rc = @@RowCountPRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy  Evaluation_FailureDetails Table'IF @ApplyFix = 1BEGINPRINT  ' 'PRINT  CONVERT(varchar(30), getdate(),121) + '  Start Processing  Policy Failures'WHILE (@failure_id <= @rc)BEGINBEGIN TRYSELECT    @EvalPolicy      = EvalPolicy,@Target         = Target,@EvalResults = EvalResults,@EvalDateTime       = EvalDateTimeFROMdbo.PolicyEvaluation_FailureDetailsWHEREfailure_id    = @failure_idSELECT @EvaluationMode           = EvaluationModeFROM   msdb.dbo.PolicyExecutionWHERE  EvalPolicy            = @EvalPolicy-----------------------------------------------------------------------IF @EvalPolicy = 'Server DDL Triggers Enabled'here is the code that fixes this policy-----------------------------------------------------------------------IF @EvalPolicy = 'Database DDL Triggers Enabled'BEGIN--SQLSERVER:SQLGARYZAIK5SQL2K8DatabasesFinanceSET @DB_Name  = RIGHT(@Target,CHARINDEX('',REVERSE(@Target))-1)IF OBJECT_ID('tempdb..#triggers') IS NOT NULLDROP TABLE #triggersCREATE 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)SELECTobj_name      = name,state_desc    = CASE WHEN is_disabled = 1  THEN ''DISABLED'' ELSE ''ENABLED'' Endfromsys.triggerswhereis_disabled = 1AND is_ms_shipped = 0AND 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 tSET       Result = e.resFROM   #triggers t,temp eWHERE  t.rn= e.rnUPDATE dbo.PolicyEvaluation_FailureDetailsSET       Target    = @DB_Name + ': ' + l.obj_name,EvalResults   = l.ResultFROM   #triggers     lWHERE  failure_id    = @failure_idAND       l.rn      = 1SELECT @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.ResultFROM   #triggers lWHERE  rn           > 1WHILE (@i2 <= @rc2)BEGINSELECT @obj_name = obj_name FROM #triggers WHERE rn = @i2IF @EvaluationMode = 0BEGINIF @i2 > 1UPDATE dbo.PolicyEvaluation_FailureDetailsSET       FixFlag          = 0,FixErrorMsg   = @NoActionWHERE  failure_id    = @failure_id2 + @i2 - 1ELSEUPDATE dbo.PolicyEvaluation_FailureDetailsSET       FixFlag          = 0,FixErrorMsg   = @NoActionWHERE  failure_id    = @failure_idENDELSEBEGINSET @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_nameIF @i2 > 1UPDATE dbo.PolicyEvaluation_FailureDetailsSET       FixFlag          = 1,FixErrorMsg   = @AppliedWHERE  failure_id    = @failure_id2 + @i2 - 1ELSEUPDATE dbo.PolicyEvaluation_FailureDetailsSET       FixFlag          = 1,FixErrorMsg   = @AppliedWHERE  failure_id    = @failure_idENDSET @i2 = @i2 + 1ENDDROP TABLE #triggersEND-------------------------------------------------------------END TRYBEGIN CATCHDECLARE @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_FailureDetailsSET       FixFlag          = 0,FixErrorMsg   = @ErrorMessageWHERE  failure_id    = @failure_idEND CATCHSET @failure_id = @failure_id + 1END ;WITH FixResults AS (SELECT EvalPolicy,FixFlag       = MIN(FixFlag)FROM   dbo.PolicyEvaluation_FailureDetailsGROUP BYEvalPolicy)UPDATE dbo.PolicyEvaluationSET       FixFlag          = f.FixFlagFROM   FixResults    fWHERE  PolicyEvaluation.EvalPolicy = f.EvalPolicyENDENDENDPRINT  CONVERT(varchar(30), getdate(),121) + ' *** The End ***'----------------------------------------------------------END TRYBEGIN CATCHPRINT '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 CATCHGO

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 TRANSACTIONBEGIN 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 TRYBEGIN 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:

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

USE [msdb]GOBEGIN TRANDECLARE @ReturnCode INT, @schedule_uid uniqueidentifier, @schedule_id intSELECT @ReturnCode = 0IF  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 QuitWithRollbackENDIF 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 QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver    @job_name = N'Verify_Policies',    @server_name = @@ServerNameIF (@@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:SQLPolicyMyComputerMyInstance  Policies | where { $_.ScheduleUid -eq "B1594BBB-269C-4BDB-9866-C0CD8A7AE694"    } |  where { $_.Enabled -eq 1} | where {$_.AutomatedPolicyEvaluationMode   -eq 4} | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName   MyComputerMyInstance',   @flags=0IF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  COMMIT TRANSACTIONPRINT 'JOB Verify_Policies WAS CREATED SUCCESSFULLY'GOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION PRINT 'COULD NOT CREATE JOB Verify_Policies'ENDEndSave: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]GOBEGIN TRANDECLARE @ReturnCode INT, @schedule_uid uniqueidentifier,  @schedule_id int, @dummy_job_id uniqueidentifierSELECT @ReturnCode = 0IF 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 QuitWithRollbackENDDECLARE @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 NULLBEGIN   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_idENDELSEBEGIN   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 QuitWithRollbackEND 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 intselect @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 = @stepsWHILE (@i > 0)BEGIN    EXEC dbo.sp_delete_jobstep      @job_name = N'Configure_Verify_Policies',      @step_id = @i ;     SET @i = @i - 1END/******   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=0IF (@@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''SoftwareMicrosoftMSSQLServerSQLServerAgent'', N''ErrorLogFile'',  @DataLocation OUTPUTSELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX('''',REVERSE(@DataLocation)))SET       @OutputFile      = @DataLocation + ''Verify_Policies.log'' SELECT @TargetSrv = @@ServerNameSELECT @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 '' + @TargetSrvELSE   SELECT @CommandString = N''dir SQLSERVER:SQLPolicy'' +  @TargetSrv + ''DEFAULTPolicies | 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=0IF (@@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=0IF (@@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 TRANSACTIONPRINT  'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'GOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0)BEGIN   ROLLBACK TRANSACTION   PRINT  'COULD NOT CREATE JOB Configure_Verify_Policies'ENDEndSave: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:

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 FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLLog).

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.