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. IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyExecution')
  5. DROP TABLE \[dbo\].\[PolicyExecution\]
  6. GO
  7. CREATE TABLE \[dbo\].\[PolicyExecution\](
  8. \[PolicyExecutionID\] \[bigint\] NOT NULL IDENTITY(1,1),
  9. \[EvalPolicy\] \[varchar\](500) NOT NULL,
  10. \[EvaluationMode\] \[int\] NOT NULL DEFAULT(0),
  11. CONSTRAINT PK_PolicyExecution PRIMARY KEY (PolicyExecutionID),
  12. CONSTRAINT UQ_PolicyExecution UNIQUE (EvalPolicy),
  13. CHECK (EvaluationMode >= 0 and EvaluationMode <= 1)
  14. ) ON \[PRIMARY\]
  15. IF @@ERROR = 0
  16. PRINT 'TABLE PolicyExecution IN msdb CREATED SUCCESSFULLY'
  17. ELSE
  18. PRINT 'COULD NOT CREATE TABLE PolicyExecution IN msdb'
  19. 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. IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation_FailureDetails')
  23. DROP TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\]
  24. GO
  25. CREATE TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\](
  26. \[failure_id\] \[int\] NOT NULL,
  27. \[EvalPolicy\] \[varchar\](500) NOT NULL,
  28. \[Target\] \[varchar\](400) NOT NULL,
  29. \[EvalDateTime\] \[datetime\] NULL,
  30. \[EvalResults\] \[xml\] NULL,
  31. \[FixFlag\] \[int\] NULL,
  32. \[FixErrorMsg\] \[nvarchar\](max) NULL,
  33. CONSTRAINT PK_PolicyEvaluation_FailureDetails PRIMARY KEY (failure_id)
  34. ) ON \[PRIMARY\]
  35. IF @@ERROR = 0
  36. PRINT 'TABLE PolicyEvaluation_FailureDetails IN msdb CREATED SUCCESSFULLY'
  37. ELSE
  38. PRINT 'COULD NOT CREATE TABLE PolicyEvaluation_FailureDetails IN msdb'
  39. 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. BEGIN TRY
  4. Declare @ReturnCode int,
  5. @schedule_uid uniqueidentifier,
  6. @schedule_id int,
  7. @pol_id int
  8. SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  9. _localserver_view WHERE name = N'Verify_Policies_Schedule'
  10. IF @schedule_uid IS NULL
  11. BEGIN
  12. SELECT @ReturnCode = 0
  13. EXEC @ReturnCode = msdb.dbo.sp_add_schedule
  14. @schedule_name = N'Verify_Policies
  15. _Schedule'
  16. ,@enabled = 1
  17. ,@freq_type = 8 --weekly
  18. ,@freq_interval = 1 --Sunday
  19. ,@freq_subday_type = 1 --at the specific time
  20. ,@freq_subday_interval = 0
  21. ,@freq_relative_interval = 0
  22. ,@freq_recurrence_factor = 1 --once a week
  23. ,@active_start_date = 20080101
  24. ,@active_end_date = 99991231
  25. ,@active_start_time = 500 --00:05:00
  26. ,@active_end_time = 235959
  27. ,@owner_login_name = 'sa'
  28. ,@schedule_uid = @schedule_uid OUTPUT
  29. ,@schedule_id = @schedule_id OUTPUT
  30. IF (@ReturnCode <> 0)
  31. BEGIN
  32. PRINT 'COULD NOT CREATE SCHEDULE. TRANSACTION
  33. ROLLED BACK'
  34. GOTO QuitWithRollback
  35. END
  36. END
  37. SELECT @pol_id = policy_id FROM msdb.dbo.syspolicy_policies WHERE
  38. name = N'Database DDL Triggers Enabled'
  39. IF @pol_id IS NOT NULL
  40. EXEC msdb.dbo.sp_syspolicy_update_policy
  41. @policy_id = @pol_id,
  42. @execution_mode = 4,
  43. @is_enabled = True,
  44. @schedule_uid = @schedule_uid
  45. /* Here you would insert similar code for other policies */
  46. COMMIT TRAN
  47. PRINT 'SUCCESSFULLY CHANGED EVALUATION MODE FOR ALL POLICIES
  48. TO On Schedule'
  49. END TRY
  50. BEGIN CATCH
  51. PRINT 'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'
  52. PRINT ''
  53. SELECT
  54. ERROR_NUMBER() AS ErrorNumber
  55. ,ERROR_SEVERITY() AS ErrorSeverity
  56. ,ERROR_STATE() AS ErrorState
  57. ,ERROR_LINE() AS ErrorLine
  58. ,ERROR_MESSAGE() AS ErrorMessage;
  59. IF @@TRANCOUNT > 0
  60. ROLLBACK TRANSACTION;
  61. END CATCH
  62. QuitWithRollback:
  63. 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. IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Verify_Policies')
  7. BEGIN
  8. EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name=N'Verify_Policies',
  9. @delete_unused_schedule=0
  10. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  11. END
  12. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'\[Uncategorized
  13. (Local)\]' AND category_class=1)
  14. BEGIN
  15. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
  16. @name=N'\[Uncategorized (Local)\]'
  17. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  18. END
  19. DECLARE @jobId BINARY(16)
  20. EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Verify_Policies',
  21. @enabled=1,
  22. @notify_level_eventlog=0,
  23. @notify_level_email=0,
  24. @notify_level_netsend=0,
  25. @notify_level_page=0,
  26. @delete_level=0,
  27. @description=N'No description available.',
  28. @category_name=N'\[Uncategorized (Local)\]',
  29. @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  30. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  31. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
  32. @job_name = N'Verify_Policies',
  33. @server_name = @@ServerName
  34. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  35. /****** Step \[Evaluate policies.\] ******/
  36. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Evaluate
  37. policies',
  38. @step_id=1,
  39. @cmdexec_success_code=0,
  40. @on_success_action=4, --go to step 2
  41. @on_success_step_id=2,
  42. @on_fail_action=2,
  43. @on_fail_step_id=0,
  44. @retry_attempts=0,
  45. @retry_interval=0,
  46. @os_run_priority=0, @subsystem=N'PowerShell',
  47. @command=N'dir SQLSERVER:\SQLPolicy\MyComputer\MyInstance\
  48. Policies | where \{ $_.ScheduleUid -eq "B1594BBB-269C-4BDB-9866-C0CD8A7AE694"
  49. \} | where \{ $_.Enabled -eq 1\} | where \{$_.AutomatedPolicyEvaluationMode
  50. -eq 4\} | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName
  51. MyComputer\MyInstance',
  52. @flags=0
  53. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  54. /****** Step \[Fix the Problems\] ******/
  55. DECLARE @command2 nvarchar(max)
  56. SELECT @command2 = N'EXEC dbo.ApplyPolicies ''' + CONVERT(varchar(30),
  57. getdate(),120) + ''''
  58. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fix
  59. the Problems (if possible).',
  60. @step_id=2,
  61. @cmdexec_success_code=0,
  62. @on_success_action=1,
  63. @on_success_step_id=0,
  64. @on_fail_action=2,
  65. @on_fail_step_id=0,
  66. @retry_attempts=0,
  67. @retry_interval=0,
  68. @os_run_priority=0, @subsystem=N'TSQL',
  69. @command= @command2,
  70. @database_name=N'msdb',
  71. @flags=0
  72. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  73. COMMIT TRANSACTION
  74. PRINT 'JOB Verify_Policies WAS CREATED SUCCESSFULLY'
  75. GOTO EndSave
  76. QuitWithRollback:
  77. IF (@@TRANCOUNT > 0)
  78. BEGIN
  79. ROLLBACK TRANSACTION
  80. PRINT 'COULD NOT CREATE JOB Verify_Policies'
  81. END
  82. EndSave:
  83. 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. DECLARE @jobId BINARY(16)
  15. SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  16. _localserver_view WHERE name = N'Verify_Policies_Schedule'
  17. SELECT @dummy_job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name
  18. = 'syspolicy_check_schedule_' + CONVERT(char(36),@schedule_uid)
  19. IF @dummy_job_id IS NOT NULL
  20. BEGIN
  21. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id=@dummy_job_id,
  22. @new_name = N'Configure_Verify_Policies',
  23. @enabled=1,
  24. @notify_level_eventlog=0,
  25. @notify_level_email=0,
  26. @notify_level_netsend=0,
  27. @notify_level_page=0,
  28. @delete_level=0,
  29. @description=N'No description available.',
  30. @category_name=N'\[Uncategorized (Local)\]',
  31. @owner_login_name=N'sa'
  32. SET @jobId=@dummy_job_id
  33. END
  34. ELSE
  35. BEGIN
  36. SELECT @jobId = job_id FROM msdb.dbo.sysjobs_view WHERE
  37. name = N'Configure_Verify_Policies'
  38. IF @jobId IS NOT NULL
  39. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id=@jobId,
  40. @enabled=1,
  41. @notify_level_eventlog=0,
  42. @notify_level_email=0,
  43. @notify_level_netsend=0,
  44. @notify_level_page=0,
  45. @delete_level=0,
  46. @description=N'No description available.',
  47. @category_name=N'\[Uncategorized (Local)\]',
  48. @owner_login_name=N'sa'
  49. ELSE
  50. EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=
  51. N'Configure_Verify_Policies',
  52. @enabled=1,
  53. @notify_level_eventlog=0,
  54. @notify_level_email=0,
  55. @notify_level_netsend=0,
  56. @notify_level_page=0,
  57. @delete_level=0,
  58. @description=N'No description available.',
  59. @category_name=N'\[Uncategorized (Local)\]',
  60. @owner_login_name=N'sa',
  61. @job_id = @jobId OUTPUT
  62. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  63. END
  64. IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers WHERE job_id = @jobId)
  65. BEGIN
  66. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
  67. @job_name = N'Configure_Verify_Policies',
  68. @server_name = @@ServerName
  69. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  70. END
  71. Declare @steps int, @i int
  72. select @steps = COUNT(*) from dbo.sysjobsteps s, dbo.sysjobs j
  73. where s.job_id = j.job_id and j.name = N'Configure_Verify_Policies'
  74. select @i = @steps
  75. WHILE (@i > 0)
  76. BEGIN
  77. EXEC dbo.sp_delete_jobstep
  78. @job_name = N'Configure_Verify_Policies',
  79. @step_id = @i ;
  80. SET @i = @i - 1
  81. END
  82. /****** Step \[Kill Running Job\] ******/
  83. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  84. @step_name=N'Kill Running Job',
  85. @step_id=1,
  86. @cmdexec_success_code=0,
  87. @on_success_action=4,
  88. @on_success_step_id=2,
  89. @on_fail_action=2,
  90. @on_fail_step_id=0,
  91. @retry_attempts=0,
  92. @retry_interval=0,
  93. @os_run_priority=0, @subsystem=N'TSQL',
  94. @command=N'
  95. IF EXISTS (SELECT j.\[name\]
  96. from msdb.dbo.sysjobactivity a
  97. join msdb.dbo.sysjobs j
  98. on j.job_id=a.job_id and j.name = ''Verify_Policies''
  99. where a.start_execution_date is not null
  100. and a.job_history_id is null)
  101. EXEC msdb.dbo.sp_stop_job N''Verify_Policies''
  102. ',
  103. @database_name=N'msdb',
  104. @flags=0
  105. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  106. /****** Step \[Configure ScheduleUid\] ******/
  107. Declare @StepString nvarchar(max)
  108. SET @StepString = N'DECLARE @TargetSrv sysname,
  109. @ScheduleUid uniqueidentifier, @sScheduleUid varchar (40),
  110. @CommandString nvarchar(max)
  111. Declare @DataLocation varchar(1000), @OutputFile varchar(1000)
  112. exec master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
  113. N''Software\Microsoft\MSSQLServer\SQLServerAgent'', N''ErrorLogFile'',
  114. @DataLocation OUTPUT
  115. SELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX(''\'',REVERSE(@DataLocation)))
  116. SET @OutputFile = @DataLocation + ''\Verify_Policies.log''
  117. SELECT @TargetSrv = @@ServerName
  118. SELECT @ScheduleUid = schedule_uid FROM msdb.dbo.sysschedules
  119. _localserver_view WHERE name = ''Verify_Policies_Schedule''
  120. SELECT @sScheduleUid= CONVERT(nchar(36),@ScheduleUid)
  121. IF CHARINDEX(''\'',@TargetSrv) > 0
  122. SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  123. @TargetSrv + ''\Policies | where \{ $_.ScheduleUid -eq "'' +
  124. @sScheduleUid + ''" \} | where \{ $_.Enabled -eq 1\} | where
  125. \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  126. -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
  127. ELSE
  128. SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  129. @TargetSrv + ''\DEFAULT\Policies | where \{ $_.ScheduleUid -eq "'' +
  130. @sScheduleUid + ''" \} | where \{ $_.Enabled -eq 1\} | where
  131. \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  132. -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
  133. EXEC msdb.dbo.sp_update_jobstep
  134. @job_name = ''Verify_Policies''
  135. ,@step_id = 1
  136. ,@command = @CommandString
  137. SELECT @CommandString = N''EXEC dbo.ApplyPolicies '''''' + CONVERT
  138. (varchar(30),getdate(),120) + '''''',1''
  139. EXEC msdb.dbo.sp_update_jobstep
  140. @job_name = ''Verify_Policies''
  141. ,@step_id = 2
  142. ,@output_file_name=@OutputFile
  143. ,@command = @CommandString '
  144. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=
  145. N'Configure ScheduleUid',
  146. @step_id=2,
  147. @cmdexec_success_code=0,
  148. @on_success_action=4,
  149. @on_success_step_id=3,
  150. @on_fail_action=2,
  151. @on_fail_step_id=0,
  152. @retry_attempts=0,
  153. @retry_interval=0,
  154. @os_run_priority=0, @subsystem=N'TSQL',
  155. @command=@StepString,
  156. @database_name=N'msdb',
  157. @flags=0
  158. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  159. /****** Step \[Run Verify_Policies Job\] ******/
  160. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  161. @step_name=N'Run Verify_Policies Job',
  162. @step_id=3,
  163. @cmdexec_success_code=0,
  164. @on_success_action=1,
  165. @on_success_step_id=0,
  166. @on_fail_action=2,
  167. @on_fail_step_id=0,
  168. @retry_attempts=0,
  169. @retry_interval=0,
  170. @os_run_priority=0, @subsystem=N'TSQL',
  171. @command=N'EXEC dbo.sp_start_job N''Verify_Policies'' ',
  172. @database_name=N'msdb',
  173. @flags=0
  174. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  175. EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
  176. @job_name = N'Configure_Verify_Policies',
  177. @schedule_name = N'Verify_Policies_Schedule' ;
  178. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  179. COMMIT TRANSACTION
  180. PRINT 'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'
  181. GOTO EndSave
  182. QuitWithRollback:
  183. IF (@@TRANCOUNT > 0)
  184. BEGIN
  185. ROLLBACK TRANSACTION
  186. PRINT 'COULD NOT CREATE JOB Configure_Verify_Policies'
  187. END
  188. EndSave:
  189. 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.