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

  1. USE msdb  
  2. SET NOCOUNT ON  
  3. GO  
  4. IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyConfiguration')  
  5.   DROP TABLE \[dbo\].\[PolicyConfiguration\]  
  6. GO  
  7. CREATE TABLE \[dbo\].\[PolicyConfiguration\](  
  8. \[PolicyConfigurationID\] \[bigint\] NOT NULL IDENTITY(1,1),        
  9.   \[EvalPolicy\] \[varchar\](500) NOT NULL,        
  10.   \[Target\] \[varchar\](400) NOT NULL,        
  11.   \[IncludeFlag\] \[int\] NULL,     --Include = 1, Exclude = 2        
  12.   CONSTRAINT PK_PolicyConfiguration PRIMARY KEY (PolicyConfigurationID),        
  13.   CONSTRAINT UQ_PolicyConfiguration UNIQUE (EvalPolicy, Target)  
  14. ) ON \[PRIMARY\]  
  15. IF @@ERROR = 0  
  16.   PRINT 'TABLE PolicyConfiguration IN msdb CREATED SUCCESSFULLY'  
  17. ELSE  
  18. PRINT 'COULD NOT CREATE TABLE PolicyConfiguration IN msdb'  
  19. 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:

  1. INSERT dbo.PolicyConfiguration (EvalPolicy,    
  2. Target, IncludeFlag)  
  3. VALUES ('Blank Password For SQL Logins',    
  4. '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)
  1. USE msdb  
  2. SET NOCOUNT ON  
  3. GO    
  4.  
  5.  IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyExecution')    
  6.   DROP TABLE \[dbo\].\[PolicyExecution\]  
  7. GO  
  8. CREATE TABLE \[dbo\].\[PolicyExecution\](        
  9.   \[PolicyExecutionID\] \[bigint\] NOT NULL IDENTITY(1,1),      
  10.   \[EvalPolicy\] \[varchar\](500) NOT NULL,        
  11.   \[EvaluationMode\] \[int\] NOT NULL DEFAULT(0),      
  12.   CONSTRAINT PK_PolicyExecution PRIMARY KEY (PolicyExecutionID),      
  13.   CONSTRAINT UQ_PolicyExecution UNIQUE (EvalPolicy),      
  14.   CHECK (EvaluationMode >= 0 and EvaluationMode <= 1)  
  15. ) ON \[PRIMARY\]  
  16. IF @@ERROR = 0         
  17.   PRINT 'TABLE PolicyExecution IN msdb CREATED SUCCESSFULLY'  
  18. ELSE   
  19. PRINT   'COULD NOT CREATE TABLE PolicyExecution IN msdb'  
  20. 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:

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

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

  1. INSERT dbo.PolicyExecution (EvalPolicy,    
  2. EvaluationMode)  
  3. VALUES ('Blank Password    
  4. 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.

  1. USE msdb
  2. GO
  3. IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation')
  4.      DROP TABLE \[dbo\].\[PolicyEvaluation\]
  5. GO
  6. CREATE TABLE \[dbo\].\[PolicyEvaluation\](
  7.   \[rec_id\] \[int\] NOT NULL,
  8.   \[history_id\] \[bigint\] NOT NULL,
  9.   \[policy_id\] \[int\] NOT NULL,
  10.   \[EvalPolicy\] \[varchar\](500) NOT NULL,
  11.   \[EvalDateTime\] \[datetime\] NULL,
  12.   \[SuccessFlag\] \[int\] NULL,
  13.   \[FixFlag\] \[int\] NULL,
  14.   \[ErrorMsg\] \[nvarchar\](max) NULL,
  15.   CONSTRAINT PK_PolicyEvaluation PRIMARY KEY (rec_id)
  16. ) ON \[PRIMARY\]
  17. IF @@ERROR = 0
  18.     PRINT 'TABLE PolicyEvaluation IN msdb CREATED SUCCESSFULLY'
  19. ELSE
  20.     PRINT 'COULD NOT CREATE TABLE PolicyEvaluation IN msdb'
  21. GO
  22.  
  23. IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation_FailureDetails')
  24.     DROP TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\]
  25. GO
  26. CREATE TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\](
  27.   \[failure_id\] \[int\] NOT NULL,
  28.   \[EvalPolicy\] \[varchar\](500) NOT NULL,
  29.   \[Target\] \[varchar\](400) NOT NULL,
  30.   \[EvalDateTime\] \[datetime\] NULL,
  31.   \[EvalResults\] \[xml\] NULL,
  32.   \[FixFlag\] \[int\] NULL,
  33.   \[FixErrorMsg\] \[nvarchar\](max) NULL,
  34.   CONSTRAINT PK_PolicyEvaluation_FailureDetails PRIMARY KEY (failure_id)
  35. ) ON \[PRIMARY\]
  36. IF @@ERROR = 0
  37.     PRINT 'TABLE PolicyEvaluation_FailureDetails IN msdb CREATED SUCCESSFULLY'
  38. ELSE
  39.     PRINT 'COULD NOT CREATE TABLE PolicyEvaluation_FailureDetails IN msdb'
  40. 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.
    1. SELECT COUNT(*)  
    2. FROM  
    3.   sys.triggers  
    4. WHERE  
    5. is_disabled = 1
    6. AND is_ms_shipped = 0  
    7. and parent_class_desc = 'DATABASE'  
    8. and name IN (SELECT Target FROM    
    9.   msdb.dbo.PolicyConfiguration WHERE    
    10. EvalPolicy = 'Database DDL Triggers
    11.   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.

    1. SELECT Statement    
    2. ExecuteSql ('Numeric', 'SELECT COUNT(*)  
    3. FROM    
    4.   sys.triggers  
    5. WHERE          
    6.   is_disabled = 1  
    7. AND is_ms_shipped = 0  
    8. and parent_class_desc = 'DATABASE'  
    9. and name IN (SELECT Target FROM    
    10.   msdb.dbo.PolicyConfiguration WHERE    
    11.   EvalPolicy = 'Database DDL    
    12.   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:

  1. @StartTime     
  2. 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.
  1. USE msdb
  2. GO
  3. IF OBJECT_ID ( 'dbo.ApplyPolicies', 'P' ) IS NOT NULL
  4. DROP PROCEDURE dbo.ApplyPolicies
  5. GO
  6. Create Procedure dbo.ApplyPolicies
  7. (
  8. @StartTime    datetime
  9. ,@ApplyFix    int    = 1
  10. )
  11. AS
  12. /*
  13. If @ApplyFix = 1, SP will try to apply policy fix
  14. If @ApplyFix = 0, SP will not try to apply policy fix
  15. */
  16. SET NOCOUNT ON;
  17. DECLARE       @failure_id          int
  18. ,@rc                 int
  19. ,@EvalPolicy  varchar(500)
  20. ,@Target             nvarchar(400)
  21. ,@EvalResults xml
  22. ,@EvalDateTime       datetime
  23. ,@NoAction           varchar(100)
  24. ,@NeedRestart varchar(100)
  25. ,@Applied            varchar(100)
  26. ,@Disabled           varchar(100)
  27. ,@temp               nvarchar(max)
  28. ,@temp2                    nvarchar(max)
  29. ,@DB_Name            nvarchar(128)
  30. ,@obj_name           nvarchar(300)
  31. ,@Login                    nvarchar(128)
  32. ,@type               varchar(5)
  33. ,@Permission  nvarchar(128)
  34. ,@ParmDef            nvarchar(500)
  35. ,@rc2                int
  36. ,@i2                 int
  37. ,@failure_id2 int
  38. ,@EvaluationMode int
  39. BEGIN TRY
  40. SELECT @NoAction            = 'No Action Taken'
  41. ,@Applied            = 'Policy Applied'
  42. ,@NeedRestart = 'Change Applied. The setting takes effect
  43.   after the server is restarted'
  44. ,@Disabled           = 'Login Disabled'
  45. TRUNCATE TABLE dbo.PolicyEvaluation_FailureDetails;
  46. TRUNCATE TABLE dbo.PolicyEvaluation;
  47. PRINT  CONVERT(varchar(30), getdate(),121) + '  Truncated
  48.   PolicyEvaluation Tables'
  49. IF EXISTS(SELECT 1 FROM dbo.syspolicy_policy_execution_history WHERE
  50.   start_date >= @StartTime)
  51. BEGIN
  52. WITH LatestEval AS
  53. (
  54. SELECT
  55. history_id,
  56. policy_id,
  57. rn = ROW_NUMBER() OVER (PARTITION BY policy_id ORDER BY start_date Desc)
  58. FROM
  59. dbo.syspolicy_policy_execution_history
  60. WHERE
  61. start_date >= @StartTime   --'10/17/2008'
  62. )
  63. INSERT INTO dbo.PolicyEvaluation
  64. (\[rec_id\]
  65. ,\[history_id\]
  66. ,\[policy_id\]
  67. ,\[EvalPolicy\]
  68. ,\[EvalDateTime\]
  69. ,\[SuccessFlag\]
  70. ,\[FixFlag\]
  71. ,\[ErrorMsg\])
  72. SELECT
  73. \[rec_id\]             = ROW_NUMBER() OVER (ORDER BY p.name Asc),
  74. \[history_id\]  = s.history_id,
  75. \[policy_id\]          = s.policy_id,
  76. \[EvalPolicy\]  = p.name,
  77. \[EvalDateTime\]       = s.start_date,
  78. \[SuccessFlag\] = s.result,
  79. \[FixFlag\]            = NULL,
  80. \[ErrorMsg\]           = s.exception
  81. FROM
  82. LatestEval    l
  83. INNER JOIN dbo.syspolicy_policy_execution_history s
  84. ON l.history_id = s.history_id
  85. INNER JOIN dbo.syspolicy_policies p
  86. ON s.policy_id = p.policy_id
  87. WHERE
  88. l.rn = 1
  89. PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy
  90.   Evaluation Table'
  91. IF EXISTS (SELECT 1 FROM \[dbo\].\[PolicyEvaluation\] WHERE \[SuccessFlag\] = 0)
  92. BEGIN
  93. INSERT INTO \[dbo\].\[PolicyEvaluation_FailureDetails\]
  94. (\[failure_id\]
  95. ,\[EvalPolicy\]
  96. ,\[Target\]
  97. ,\[EvalDateTime\]
  98. ,\[EvalResults\]
  99. ,\[FixFlag\]
  100. ,\[FixErrorMsg\])
  101. SELECT
  102. \[failure_id\]  = ROW_NUMBER() OVER (ORDER BY p.EvalPolicy,d.detail_id Asc)
  103. ,\[EvalPolicy\] = p.EvalPolicy
  104. ,\[Target\]            = d.target_query_expression
  105. ,\[EvalDateTime\]      = d.execution_date
  106. ,\[EvalResults\]       = d.result_detail
  107. ,\[FixFlag\]           = NULL
  108. ,\[FixErrorMsg\]       = NULL
  109. FROM
  110. dbo.PolicyEvaluation p
  111. INNER JOIN dbo.syspolicy_policy_execution_history_details d
  112. ON p.history_id = d.history_id
  113. WHERE
  114. p.SuccessFlag = 0
  115. SELECT @failure_id   = 1, @rc = @@RowCount
  116. PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy
  117.   Evaluation_FailureDetails Table'
  118. IF @ApplyFix = 1
  119. BEGIN
  120. PRINT  ' '
  121. PRINT  CONVERT(varchar(30), getdate(),121) + '  Start Processing
  122.   Policy Failures'
  123. WHILE (@failure_id <= @rc)
  124. BEGIN
  125. BEGIN TRY
  126. SELECT        @EvalPolicy          = EvalPolicy
  127. ,@Target             = Target
  128. ,@EvalResults = EvalResults
  129. ,@EvalDateTime       = EvalDateTime
  130. FROM
  131. dbo.PolicyEvaluation_FailureDetails
  132. WHERE
  133. failure_id    = @failure_id
  134. SELECT @EvaluationMode                   = EvaluationMode
  135. FROM   msdb.dbo.PolicyExecution
  136. WHERE  EvalPolicy                        = @EvalPolicy
  137. -----------------------------------------------------------------------
  138. IF @EvalPolicy = 'Server DDL Triggers Enabled'
  139. here is the code that fixes this policy
  140. -----------------------------------------------------------------------
  141. IF @EvalPolicy = 'Database DDL Triggers Enabled'
  142. BEGIN
  143. --SQLSERVER:\SQL\GARYZAIK5\SQL2K8\Databases\Finance
  144. SET @DB_Name  = RIGHT(@Target,CHARINDEX('\',REVERSE(@Target))-1)
  145. IF OBJECT_ID('tempdb..#triggers') IS NOT NULL
  146. DROP TABLE #triggers
  147. CREATE TABLE #triggers (
  148. rn                   int NOT NULL IDENTITY(1,1),
  149. obj_name             nVARCHAR(200),
  150. state_desc    nVARCHAR(10),
  151. Result        xml NULL
  152. )
  153. SELECT @temp = 'USE ' + @DB_Name + '
  154. INSERT #triggers (obj_name, state_desc)
  155. SELECT
  156. obj_name      = name,
  157. state_desc    = CASE WHEN is_disabled = 1
  158.   THEN ''DISABLED'' ELSE ''ENABLED'' End
  159. from
  160. sys.triggers
  161. where
  162. is_disabled = 1
  163. AND is_ms_shipped = 0
  164. AND parent_class_desc = ''DATABASE''
  165. AND name IN (SELECT Target FROM msdb.dbo.PolicyConfiguration
  166.   WHERE EvalPolicy = '
  167.   'Database DDL Triggers Enabled'' AND IncludeFlag = 1)
  168. '
  169. EXEC (@temp) ;
  170. SELECT @rc2 = (SELECT COUNT(*) FROM #triggers)
  171. SET           @i2 = 1 ;
  172. WITH temp AS (
  173. SELECT rn,
  174. res = (SELECT * FROM #triggers w WHERE w.rn = t.rn for xml auto )
  175. FROM #triggers t)
  176. UPDATE t
  177. SET           Result = e.res
  178. FROM   #triggers t,
  179. temp e
  180. WHERE  t.rn= e.rn
  181. UPDATE dbo.PolicyEvaluation_FailureDetails
  182. SET           Target        = @DB_Name + ': ' + l.obj_name,
  183. EvalResults   = l.Result
  184. FROM   #triggers     l
  185. WHERE  failure_id    = @failure_id
  186. AND           l.rn          = 1
  187. SELECT @failure_id2 = ISNULL((SELECT MAX(failure_id)
  188. FROM msdb.dbo.PolicyEvaluation_FailureDetails),0)
  189. INSERT msdb.dbo.PolicyEvaluation_FailureDetails (failure_id,EvalPolicy,
  190.   Target,EvalDateTime,EvalResults)
  191. SELECT failure_id    = @failure_id2 + rn - 1,
  192. EvalPolicy    = @EvalPolicy,
  193. Target        = @DB_Name + ': ' + l.obj_name,
  194. EvalDateTime= @EvalDateTime,
  195. EvalResults   = l.Result
  196. FROM   #triggers l
  197. WHERE  rn                   > 1
  198. WHILE (@i2 <= @rc2)
  199. BEGIN
  200. SELECT @obj_name = obj_name FROM #triggers WHERE rn = @i2
  201. IF @EvaluationMode = 0
  202. BEGIN
  203. IF @i2 > 1
  204. UPDATE dbo.PolicyEvaluation_FailureDetails
  205. SET           FixFlag              = 0,
  206. FixErrorMsg   = @NoAction
  207. WHERE  failure_id    = @failure_id2 + @i2 - 1
  208. ELSE
  209. UPDATE dbo.PolicyEvaluation_FailureDetails
  210. SET           FixFlag              = 0,
  211. FixErrorMsg   = @NoAction
  212. WHERE  failure_id    = @failure_id
  213. END
  214. ELSE
  215. BEGIN
  216. SET @temp            = N'USE ' + @DB_Name + ';
  217. ENABLE Trigger ' +  @obj_name + N' ON DATABASE'
  218. SET @ParmDef  = N'@DB_Name nvarchar(128), @obj_name nvarchar(300)'
  219. EXEC sp_executesql @temp, @ParmDef, @DB_Name, @obj_name
  220. IF @i2 > 1
  221. UPDATE dbo.PolicyEvaluation_FailureDetails
  222. SET           FixFlag              = 1,
  223. FixErrorMsg   = @Applied
  224. WHERE  failure_id    = @failure_id2 + @i2 - 1
  225. ELSE
  226. UPDATE dbo.PolicyEvaluation_FailureDetails
  227. SET           FixFlag              = 1,
  228. FixErrorMsg   = @Applied
  229. WHERE  failure_id    = @failure_id
  230. END
  231. SET @i2 = @i2 + 1
  232. END
  233. DROP TABLE #triggers
  234. END
  235. -------------------------------------------------------------
  236. END TRY
  237. BEGIN CATCH
  238. DECLARE @ErrorNumber int,
  239. @ErrorSeverity       int,
  240. @ErrorState          int,
  241. @ErrorProcedure      sysname,
  242. @ErrorLine           int,
  243. @ErrorMessage nvarchar(max)
  244. SELECT
  245. @ErrorNumber = ERROR_NUMBER()
  246. ,@ErrorSeverity = ERROR_SEVERITY()
  247. ,@ErrorState = ERROR_STATE()
  248. ,@ErrorProcedure = ERROR_PROCEDURE()
  249. ,@ErrorLine = ERROR_LINE()
  250. ,@ErrorMessage = ERROR_MESSAGE();
  251. PRINT 'Error ' + CONVERT(varchar(50), @ErrorNumber) +
  252. ', Severity ' + CONVERT(varchar(5), @ErrorSeverity) +
  253. ', State ' + CONVERT(varchar(5), @ErrorState) +
  254. ', Procedure ' + ISNULL(@ErrorProcedure, '-') +
  255. ', Line ' + CONVERT(varchar(5), @ErrorLine);
  256. PRINT @ErrorMessage;
  257. UPDATE dbo.PolicyEvaluation_FailureDetails
  258. SET           FixFlag              = 0,
  259. FixErrorMsg   = @ErrorMessage
  260. WHERE  failure_id    = @failure_id
  261. END CATCH
  262. SET @failure_id = @failure_id + 1
  263. END ;
  264. WITH FixResults AS (
  265. SELECT EvalPolicy,
  266. FixFlag       = MIN(FixFlag)
  267. FROM   dbo.PolicyEvaluation_FailureDetails
  268. GROUP BY
  269. EvalPolicy
  270. )
  271. UPDATE dbo.PolicyEvaluation
  272. SET           FixFlag              = f.FixFlag
  273. FROM   FixResults    f
  274. WHERE  PolicyEvaluation.EvalPolicy = f.EvalPolicy
  275. END
  276. END
  277. END
  278. PRINT  CONVERT(varchar(30), getdate(),121) + '
  279. *** The End ***'
  280. ----------------------------------------------------------
  281. END TRY
  282. BEGIN CATCH
  283. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
  284. ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
  285. ', State ' + CONVERT(varchar(5), ERROR_STATE()) +
  286. ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
  287. ', Line ' + CONVERT(varchar(5), ERROR_LINE());
  288. PRINT ERROR_MESSAGE();
  289. END CATCH
  290. 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.

  1. PRINT  'CREATING SCHEDULE FOR POLICY EVALUATION'
  2. BEGIN TRANSACTION
  3.  
  4. BEGIN TRY
  5.        Declare       @ReturnCode int,
  6.                            @schedule_uid uniqueidentifier,
  7.                            @schedule_id int,
  8.                            @pol_id int
  9.  
  10.        SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  11.   _localserver_view WHERE name = N'Verify_Policies_Schedule'
  12.        IF  @schedule_uid IS NULL
  13.        BEGIN
  14.               SELECT @ReturnCode = 0
  15.               EXEC @ReturnCode =  msdb.dbo.sp_add_schedule
  16.                      @schedule_name                    = N'Verify_Policies
  17.   _Schedule'
  18.                      ,@enabled                         = 1
  19.                      ,@freq_type                       = 8    --weekly
  20.                      ,@freq_interval                   = 1    --Sunday
  21.                      ,@freq_subday_type         = 1    --at the specific time
  22.                      ,@freq_subday_interval            = 0          
  23.                      ,@freq_relative_interval   = 0
  24.                      ,@freq_recurrence_factor   = 1    --once a week
  25.                      ,@active_start_date        = 20080101
  26.                      ,@active_end_date          = 99991231
  27.                      ,@active_start_time        = 500         --00:05:00
  28.                      ,@active_end_time          = 235959
  29.                      ,@owner_login_name         = 'sa'
  30.                      ,@schedule_uid                    = @schedule_uid OUTPUT
  31.                      ,@schedule_id              = @schedule_id OUTPUT
  32.            
  33.               IF (@ReturnCode <> 0)
  34.               BEGIN
  35.                      PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION
  36.   ROLLED BACK'
  37.                      GOTO QuitWithRollback
  38.               END
  39.        END  
  40.  
  41.        SELECT @pol_id = policy_id FROM msdb.dbo.syspolicy_policies WHERE
  42.   name = N'Database DDL Triggers Enabled'
  43.        IF @pol_id IS NOT NULL
  44.               EXEC msdb.dbo.sp_syspolicy_update_policy
  45.                      @policy_id                        = @pol_id,
  46.                      @execution_mode                   = 4,
  47.                      @is_enabled                       = True,
  48.                      @schedule_uid              = @schedule_uid
  49. /* Here you would insert similar code for other policies */
  50.        COMMIT TRAN
  51.        PRINT  'SUCCESSFULLY CHANGED EVALUATION MODE FOR ALL POLICIES
  52.   TO On Schedule'
  53.            
  54. END TRY
  55. BEGIN CATCH
  56.        PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'
  57.        PRINT  ''
  58.        SELECT
  59.         ERROR_NUMBER() AS ErrorNumber
  60.         ,ERROR_SEVERITY() AS ErrorSeverity
  61.         ,ERROR_STATE() AS ErrorState
  62.         ,ERROR_LINE() AS ErrorLine
  63.         ,ERROR_MESSAGE() AS ErrorMessage;
  64.  
  65.     IF @@TRANCOUNT > 0
  66.         ROLLBACK TRANSACTION;
  67. END CATCH    
  68. QuitWithRollback:  
  69. 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
    1. 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.
  1. USE \[msdb\]
  2. GO
  3. BEGIN TRAN
  4. DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier, @schedule_id int
  5. SELECT @ReturnCode = 0
  6.  
  7. IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Verify_Policies')
  8. BEGIN
  9.  EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name=N'Verify_Policies',
  10.   @delete_unused_schedule=0
  11.  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  12. END
  13.  
  14. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'\[Uncategorized
  15.   (Local)\]' AND category_class=1)
  16. BEGIN
  17.  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
  18.   @name=N'\[Uncategorized (Local)\]'
  19.  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  20. END
  21.  
  22. DECLARE @jobId BINARY(16)
  23. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Verify_Policies',
  24.   @enabled=1,
  25.   @notify_level_eventlog=0,
  26.   @notify_level_email=0,
  27.   @notify_level_netsend=0,
  28.   @notify_level_page=0,
  29.   @delete_level=0,
  30.   @description=N'No description available.',
  31.   @category_name=N'\[Uncategorized (Local)\]',
  32.   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  33. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  34.  
  35. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
  36.     @job_name = N'Verify_Policies',
  37.     @server_name = @@ServerName
  38. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  39.  
  40. /******   Step \[Evaluate policies.\]     ******/
  41.  
  42. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Evaluate
  43.   policies',
  44.   @step_id=1,
  45.   @cmdexec_success_code=0,
  46.   @on_success_action=4,  --go to step 2
  47.   @on_success_step_id=2,
  48.   @on_fail_action=2,
  49.   @on_fail_step_id=0,
  50.   @retry_attempts=0,
  51.   @retry_interval=0,
  52.   @os_run_priority=0, @subsystem=N'PowerShell',
  53.   @command=N'dir SQLSERVER:\SQLPolicy\MyComputer\MyInstance\
  54.   Policies | where \{ $_.ScheduleUid -eq "B1594BBB-269C-4BDB-9866-C0CD8A7AE694"  
  55.   \} |  where \{ $_.Enabled -eq 1\} | where \{$_.AutomatedPolicyEvaluationMode
  56.   -eq 4\} | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName
  57.   MyComputer\MyInstance',
  58.   @flags=0
  59.  
  60. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  61.  
  62. /******   Step \[Fix the Problems\]     ******/
  63. DECLARE @command2 nvarchar(max)
  64. SELECT @command2 = N'EXEC dbo.ApplyPolicies ''' + CONVERT(varchar(30),
  65.   getdate(),120) + ''''
  66.  
  67. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fix
  68.   the Problems (if possible).',
  69.   @step_id=2,
  70.   @cmdexec_success_code=0,
  71.   @on_success_action=1,
  72.   @on_success_step_id=0,
  73.   @on_fail_action=2,
  74.   @on_fail_step_id=0,
  75.   @retry_attempts=0,
  76.   @retry_interval=0,
  77.   @os_run_priority=0, @subsystem=N'TSQL',
  78.   @command= @command2,
  79.   @database_name=N'msdb',
  80.   @flags=0
  81. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  82.  
  83. COMMIT TRANSACTION
  84. PRINT 'JOB Verify_Policies WAS CREATED SUCCESSFULLY'
  85. GOTO EndSave
  86. QuitWithRollback:
  87. IF (@@TRANCOUNT > 0)
  88. BEGIN
  89.  ROLLBACK TRANSACTION
  90.  PRINT 'COULD NOT CREATE JOB Verify_Policies'
  91. END
  92. EndSave:
  93.  
  94. 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.

  1. USE \[msdb\]
  2. GO
  3. BEGIN TRAN
  4. DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier,
  5.   @schedule_id int, @dummy_job_id uniqueidentifier
  6. SELECT @ReturnCode = 0
  7. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE
  8.   name=N'\[Uncategorized (Local)\]' AND category_class=1)
  9. BEGIN
  10.        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
  11.   @type=N'LOCAL', @name=N'\[Uncategorized (Local)\]'
  12.        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  13. END
  14.  
  15. DECLARE @jobId BINARY(16)
  16.  
  17. SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  18.   _localserver_view WHERE name = N'Verify_Policies_Schedule'
  19. SELECT @dummy_job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name
  20.   = 'syspolicy_check_schedule_' + CONVERT(char(36),@schedule_uid)
  21. IF @dummy_job_id IS NOT NULL
  22. BEGIN
  23.        EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@dummy_job_id,
  24.                      @new_name = N'Configure_Verify_Policies',
  25.                      @enabled=1,
  26.                      @notify_level_eventlog=0,
  27.                      @notify_level_email=0,
  28.                      @notify_level_netsend=0,
  29.                      @notify_level_page=0,
  30.                      @delete_level=0,
  31.                      @description=N'No description available.',
  32.                      @category_name=N'\[Uncategorized (Local)\]',
  33.                      @owner_login_name=N'sa'
  34.                    
  35.        SET @jobId=@dummy_job_id
  36. END
  37. ELSE
  38. BEGIN
  39.        SELECT @jobId = job_id FROM msdb.dbo.sysjobs_view WHERE
  40.   name = N'Configure_Verify_Policies'
  41.        IF @jobId IS NOT NULL
  42.               EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@jobId,
  43.                            @enabled=1,
  44.                            @notify_level_eventlog=0,
  45.                            @notify_level_email=0,
  46.                            @notify_level_netsend=0,
  47.                            @notify_level_page=0,
  48.                            @delete_level=0,
  49.                            @description=N'No description available.',
  50.                            @category_name=N'\[Uncategorized (Local)\]',
  51.                            @owner_login_name=N'sa'
  52.        ELSE
  53.               EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=
  54.   N'Configure_Verify_Policies',
  55.                      @enabled=1,
  56.                      @notify_level_eventlog=0,
  57.                      @notify_level_email=0,
  58.                      @notify_level_netsend=0,
  59.                      @notify_level_page=0,
  60.                      @delete_level=0,
  61.                      @description=N'No description available.',
  62.                      @category_name=N'\[Uncategorized (Local)\]',
  63.                      @owner_login_name=N'sa',
  64.                      @job_id = @jobId OUTPUT
  65.  
  66.         IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  67. END
  68.  
  69. IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers WHERE job_id = @jobId)
  70. BEGIN
  71.        EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
  72.               @job_name = N'Configure_Verify_Policies',
  73.               @server_name = @@ServerName
  74.        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  75. END
  76.  
  77. Declare @steps int, @i int
  78. select @steps = COUNT(*) from dbo.sysjobsteps s, dbo.sysjobs j
  79.   where s.job_id = j.job_id and j.name = N'Configure_Verify_Policies'
  80. select @i = @steps
  81. WHILE (@i > 0)
  82. BEGIN
  83.        EXEC dbo.sp_delete_jobstep
  84.               @job_name = N'Configure_Verify_Policies',
  85.               @step_id = @i ;
  86.  
  87.        SET @i = @i - 1
  88. END
  89.  
  90. /******   Step \[Kill Running Job\]     ******/
  91. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  92.   @step_name=N'Kill Running Job',
  93.               @step_id=1,
  94.               @cmdexec_success_code=0,
  95.               @on_success_action=4,
  96.               @on_success_step_id=2,
  97.               @on_fail_action=2,
  98.               @on_fail_step_id=0,
  99.               @retry_attempts=0,
  100.               @retry_interval=0,
  101.               @os_run_priority=0, @subsystem=N'TSQL',
  102.               @command=N'
  103. IF EXISTS (SELECT j.\[name\]
  104.               from msdb.dbo.sysjobactivity a
  105.               join msdb.dbo.sysjobs j
  106.               on j.job_id=a.job_id and j.name = ''Verify_Policies''
  107.               where a.start_execution_date is not null
  108.               and a.job_history_id is null)
  109.        EXEC msdb.dbo.sp_stop_job N''Verify_Policies''
  110.               ',
  111.               @database_name=N'msdb',
  112.               @flags=0
  113. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  114.  
  115. /******   Step \[Configure ScheduleUid\]     ******/
  116. Declare @StepString  nvarchar(max)
  117. SET           @StepString = N'DECLARE    @TargetSrv    sysname,
  118.   @ScheduleUid uniqueidentifier, @sScheduleUid varchar (40),
  119.   @CommandString nvarchar(max)
  120. Declare       @DataLocation varchar(1000), @OutputFile varchar(1000)
  121. exec master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
  122. N''Software\Microsoft\MSSQLServer\SQLServerAgent'', N''ErrorLogFile'',
  123.   @DataLocation OUTPUT
  124. SELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX(''\'',REVERSE(@DataLocation)))
  125. SET           @OutputFile          = @DataLocation + ''\Verify_Policies.log''
  126.  
  127. SELECT @TargetSrv = @@ServerName
  128. SELECT @ScheduleUid = schedule_uid FROM msdb.dbo.sysschedules
  129.   _localserver_view WHERE name = ''Verify_Policies_Schedule''
  130. SELECT @sScheduleUid= CONVERT(nchar(36),@ScheduleUid)
  131. IF CHARINDEX(''\'',@TargetSrv) > 0
  132.        SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  133.   @TargetSrv + ''\Policies | where \{ $_.ScheduleUid -eq "'' +
  134.   @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where
  135.   \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  136.   -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
  137. ELSE
  138.        SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  139.   @TargetSrv + ''\DEFAULT\Policies | where \{ $_.ScheduleUid -eq "'' +
  140.   @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where
  141.   \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  142.   -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
  143.  
  144. EXEC msdb.dbo.sp_update_jobstep
  145.        @job_name = ''Verify_Policies''
  146.        ,@step_id = 1
  147.        ,@command = @CommandString
  148.      
  149. SELECT @CommandString = N''EXEC dbo.ApplyPolicies '''''' + CONVERT
  150.   (varchar(30),getdate(),120) + '''''',1''
  151. EXEC msdb.dbo.sp_update_jobstep
  152.        @job_name = ''Verify_Policies''
  153.        ,@step_id = 2
  154. ,@output_file_name=@OutputFile  
  155.        ,@command = @CommandString        '
  156.  
  157. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=
  158.   N'Configure ScheduleUid',
  159.               @step_id=2,
  160.               @cmdexec_success_code=0,
  161.               @on_success_action=4,
  162.               @on_success_step_id=3,
  163.               @on_fail_action=2,
  164.               @on_fail_step_id=0,
  165.               @retry_attempts=0,
  166.               @retry_interval=0,
  167.               @os_run_priority=0, @subsystem=N'TSQL',
  168.               @command=@StepString,
  169.               @database_name=N'msdb',
  170.               @flags=0
  171. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  172.  
  173. /******   Step \[Run Verify_Policies Job\]     ******/
  174. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  175.               @step_name=N'Run Verify_Policies Job',
  176.               @step_id=3,
  177.               @cmdexec_success_code=0,
  178.               @on_success_action=1,
  179.               @on_success_step_id=0,
  180.               @on_fail_action=2,
  181.               @on_fail_step_id=0,
  182.               @retry_attempts=0,
  183.               @retry_interval=0,
  184.               @os_run_priority=0, @subsystem=N'TSQL',
  185.               @command=N'EXEC dbo.sp_start_job N''Verify_Policies'' ',
  186.               @database_name=N'msdb',
  187.               @flags=0
  188. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  189.  
  190. EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
  191.    @job_name = N'Configure_Verify_Policies',
  192.    @schedule_name = N'Verify_Policies_Schedule' ;
  193. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  194.  
  195. COMMIT TRANSACTION
  196. PRINT  'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'
  197. GOTO EndSave
  198. QuitWithRollback:
  199. IF (@@TRANCOUNT > 0)
  200. BEGIN
  201.        ROLLBACK TRANSACTION
  202.        PRINT  'COULD NOT CREATE JOB Configure_Verify_Policies'
  203. END
  204. EndSave:
  205. 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.

  1. BEGIN TRANSACTION  
  2. DECLARE @ReturnCode INT  
  3. EXEC msdb.dbo.sp_syspolicy_configure @name=    
  4.   Enabled, @value=1  
  5. EXEC msdb.dbo.sp_syspolicy_configure @name=    
  6.   N'HistoryRetentionInDays', @value=15  
  7. EXEC @ReturnCode =  msdb.dbo.sp_update_job    
  8.   @job_name=N'syspolicy_purge_history',    
  9.   @enabled=1  
  10. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO    
  11.   QuitWithRollback  
  12. COMMIT TRANSACTION  
  13. GOTO EndSave  
  14. QuitWithRollback:      
  15.   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
  16. 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:

  1. INSERT dbo.PolicyConfiguration (EvalPolicy,    
  2. Target, IncludeFlag)    
  3. VALUES (‘Database DDL Triggers Enabled’,    
  4. ‘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:

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

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

  1. select * from msdb.dbo.PolicyEvaluation    
  2. order by EvalPolicy  select * from msdb.dbo.PolicyEvaluation    
  3. _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.