Построение управляемой политиками системы SQL Server 2008

Об управлении на основе политик

Управление, основанное на политиках, в SQL Server 2008 осуществляется через набор правил, которые устанавливает администратор базы данных для проверки, удовлетворяют ли заданной политике целевые объекты, такие как серверы, базы данных и таблицы. Поддающиеся проверке свойства целевых объектов предъявляются через предопределенные объекты, фасеты, изменять которые пользователь не имеет возможности.

Состояние свойства фасета проверяется через логическое выражение, которое может задавать пользователь и которое называется условием. Условие определяет допустимое состояние фасета. Множественные свойства этого же фасета могут быть определены одним условием, с использованием булевых операторов AND и OR. Каждая политика может обладать только одним условием, контролирующим заданные цели.

Все политики могут быть выполнены в режимах по требованию On demand или по расписанию On schedule. Некоторые политики поддерживают режим «при изменении — только протоколировать», On change: log only. И лишь немногие политики могут быть настроены на режим «при изменении — предотвращать», On change: prevent.

Я предполагаю, что читатель знаком с основами схемы управления политиками для SQL Server 2008 и с ее основными компонентами: политиками, условиями и фасетами. Детальную информацию по вопросу можно найти в статье «Administering Servers by Using Policy-Based Management» в руководстве SQL Server Books Online (BOL).

Насущная проблема

Один из моих корпоративных клиентов в финансовой индустрии попросил помочь ему в разработке системы контроля, основанной на управлении политиками; система должна была контролировать все требования безопасности для существующих и новых установок SQL Server 2008. В данной компании за написание сценариев для всех бизнес-департаментов под общие установки SQL Server 2008 без сопровождения отвечает служба инженеров Windows. Эта техническая служба планировала унифицировать критерии безопасности для всех серверов компании версии 2008, независимо от окружения, приложения и модели поддержки.

Мне предоставили обобщенный список требований безопасности, который, как предполагалось, следовало преобразовать в политики. Подавляющее большинство требований было основано на лучших практиках Microsoft, а некоторые были специфичными для компании. Все политики должны были быть достаточно гибкими, чтобы при необходимости предоставлять возможность администратору базы данных вводить исключения, однако без редактирования политик. SQL Server 2008 поставляется с набором встроенных политик. Эти политики не установлены по умолчанию, но могут легко импортироваться на сервер. За подробностями по данной теме предлагаю читателям обратиться к статье «How to: Export and Import a Policy-Based Management Policy» в руководстве BOL.

К сожалению, в начальном виде реализация основанного на политиках управления в SQL Server 2008 имеет ряд ограничений.

Политики недостаточно гибкие. Сложно построить обобщенную политику, общую для всех отдельных серверов, которые поддерживает администратор базы данных.
Лишь небольшое число политик допускает режим On change: prevent, и у администратора базы данных нет средств заставить соблюдать политику.
Во встроенных политиках реализованы только самые простые правила.

Описание решения

Я обошел ограничения изначальной реализации, создав решение на базе таблиц, которое позволяет администратору базы данных добавлять исключения для политики и контролировать ее выполнение. В рамках своего решения я построил механизм принудительного обеспечения политики через запланированное задание, которое оценивает политику и в случае необходимости и запроса немедленно реализует ее.

После консультаций с клиентом я построил расписание проверки политик Verify_Policies_Schedule. Все разработанные политики были ассоциированы с режимом On schedule evaluation, а также связаны с озвученным выше расписанием. Если хотя бы одна политика планируется к выполнению, то SQL Server генерирует задание. Я изменяю это созданное системой задание, повышая гибкость и добавляя дополнительный шаг, обеспечивающий соблюдение политики, если будет обнаружено нарушение.

К базе данных msdb я добавил четыре новые таблицы для хранения в них настроек политик, необходимого режима выполнения и результатов оценок политики.

Таблица dbo.PolicyConfiguration. Исключения из регулярных условий политики могут быть занесены в таблицу dbo.PolicyConfiguration (см. листинг 1, воссоздающий эту таблицу). Чтобы добавить исключение в политику (для конкретных сервера, базы данных или объекта), администратор базы данных должен произвести запись в эту таблицу.

В столбцах таблицы хранятся следующие данные:

PolicyConfigurationID — первичный ключ;
EvalPolicy — наименование политики;
Target — имя объекта (базы данных, сервера), который надо включить или исключить из сферы политики;
IncludeFlag — 1, если объект включается; 2, если объект исключается.

Например, если вы хотите сделать исключение к политике Blank Password For SQL Logins (пустой пароль при регистрации в SQL Server) на сервере ServerA, то надо добавить следующую запись в таблицу dbo.PolicyConfiguration:

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

Каждая политика может проверяться в одном из двух режимов. Режим Mode Value 0 означает режим просмотра Display Only. Он служит для проверки соблюдения политики, а обнаружение нарушений не влечет за собой принудительного ее исполнения. Режим Mode Value 1 представляет режим навязывания политики Enforce Policy, в котором политика реализуется, как только обнаружено ее нарушение.

Таблица dbo.PolicyExecution. Тип режима оценки исполнения индивидуальной политики может быть установлен в таблице dbo.PolicyExecution (за создание таблицы отвечает листинг 2). Столбцы этой таблицы хранят следующие данные:

PolicyExecutionID — первичный ключ;
EvalPolicy — имя политики;
EvaluationMode — 0 (только просмотр); 1 (исполнение политики).

Например, чтобы увидеть, была ли нарушена политика Blank Password For SQL Logins, не осуществляя принудительное назначение пароля, добавьте в таблицу dbo.Policy

Execution запись:

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

Для того чтобы немедленно провести эту политику, с привязкой к паролю, заданному по умолчанию, добавьте в таблицу dbo.PolicyExecution запись:

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

Таблицы dbo.PolicyEvaluation и dbo.PolicyEvaluation_FailureDetails. Во время проверки выполнения политик на SQL Server их результаты аккумулируются в двух системных таблицах в базе данных msdb: dbo.syspolicy_policy_execution_history и dbo.syspolicy_policy_execution_history_details.

Созданное мной задание выбирает из таблицы dbo.syspolicy_policy_execution_history последние результаты проверки запланированных политик и сохраняет их в новой таблице, которая называется msdb.dbo.PolicyEvaluation.

В то же время последние нарушения оцениваемых политик извлекаются из системной таблицы dbo.syspolicy_policy_execution_history_details и сохраняются в таблице msdb.dbo.PolicyEvaluation_FailureDetails. Код создания этих двух таблиц показан в листинге 3.

В представленной в статье таблице приведены имена и описания полей таблиц базы данных msdb: dbo.PolicyEvaluation и dbo.PolicyEvaluation_FailureDetails. Для краткости в этой описательной таблице в столбце под названием «Таблица» единица (1) обозначает таблицу dbo.PolicyEvaluation, а двойка (2) — таблицу dbo.PolicyEvaluation_FailureDetails.

Таблица. Описание столбцов таблицы dbo.PolicyEvaluation (*) и таблицы dbo.PolicyEvaluation_FailureDetails (^)

Создание политики

Для иллюстрации техники, которую я применял при построении политик, рассмотрим, как была создана политика Database DDL Triggers Enabled. В компании используется основанный на триггерах процесс сбора информации о каждом пользователе, регистрировавшемся в любой из баз данных, за исключением tempdb.

Мне было предложено создать политику для проверки, были ли активизированы все обязательные триггеры DDL в каждой базе данных на каждом сервере SQL Server 2005 (или более новых версиях). Для этого я должен был выполнить следующие шаги:

Принять решение об имени политики. Нам необходимо знать имя каждой политики, чтобы в таблице msdb.dbo.PolicyConfiguration вводить для них исключения, если потребуется.
Принять решение относительно серверных ограничений. Так как триггеры DDL были введены в версии SQL Server 2005, нам следовало включить соответствующий фильтр.
Принять решение об именах баз данных. На основе фасета баз данных я составил условие No tempdb, включающее все пользовательские базы данных и три оставшиеся системные базы данных. Это условие, представленное на экране 1, также позволяет убедиться, что статус каждой базы данных есть Normal.
Создать условие проверки наличия неактивных или недоступных триггеров. Здесь можно выбрать любой фасет, позволяющий проверять условия на объектах баз данных, например Database или Database Security. В листинге 4 представлено выражение, определяющее количество триггеров DDL, созданных пользователями, активных в конкретной базе данных. Затем мы применяем функцию ExecuteSQL, которая допускает вставку предложения SELECT в PBM-выражение, как показано в листинге 5. Я использовал это выражение для условия Required Database DDL Triggers Enabled соответствующей политики (экран 2). Заметим, что в область окна на экране помещается только начало оператора.
Создать требуемую политику. Вы создаете политику Database DDL Triggers Enabled, определяя проверочное условие, цель, серверные ограничения и режим проверки (в данном случае On demand). Также вы имеете возможность ввести описание политики и назначить ей категорию на закладке Description, что показано на экране 3.
Создать сценарий для политики. Пишите как можно больше установочных сценариев, чтобы была возможность воспроизводить их действие, если потребуется. В моем случае выполнение сценария, устанавливающего все политики, стало частью процесса установки SQL Server на всех модулях. Разработчики Microsoft обеспечили возможность кодировать как политики, так и условия, но все-таки в предоставляемом ими средстве существуют определенные проблемы.

Не обеспечена запись политики и условий, лежащих в ее основе, в одном выходном файле, таким образом, вы сами должны объединить вывод всех трех условий и политики непосредственно в конечный сценарий.
Генерируемый сценарий отказа от политики drop policy не обращает внимания на условия, на которые ссылаются из других политик. Это утверждение относится к условиям, используемым как ограничения целей или сервера.

Экран 1. Условие No tempdb

Экран 2. Задание открытого условия с проверкой активности триггеров DDL

Экран 3. Вкладка с описанием активных триггеров DDL в базе данных

Несмотря на это, средство Microsoft для кодирования сценариев весьма полезно. Без него вы обнаружили бы, как сложно записывать (в правильном формате) команды, создающие необходимые объекты.

Реализация политики

Теперь давайте посмотрим, как обеспечивается выполнение политик. Согласно расписанию, мы выполняем задание, состоящее из двух шагов. На первом шаге оцениваются все активные политики на сервере. Второй шаг для каждой настроенной политики устраняет нарушения политик с помощью запуска хранимой процедуры (со множественными операторами CASE внутри для каждой политики).

По замыслу в каждый момент времени, когда оценивается политика (либо по требованию, либо по расписанию), SQL Server сохраняет итоговые результаты оценок в системной таблице msdb.dbo.syspolicy_policy_execution_history. Кроме того, отклонения от политики при проверке конкретного объекта-цели сохраняются в другой системной таблице — msdb.dbo.syspolicy_policy_execution_history_details.

Мы можем проанализировать неисполнение политик последовательно, по одной записи, и принять меры к их исправлению. Для этого в базе данных msdb была создана хранимая процедура под названием dbo.ApplyPolicies. Процедура с одним параметром:

@StartTime datetime.

Этот параметр определяет начало временного интервала для таблицы msdb.dbo.syspolicy_policy_execution_history, в которой содержатся записи текущих оценок политик. В таблице хранятся все неудаленные результаты оценки политик (число которых соответствует количеству запусков).

Поскольку мне были нужны лишь последние результаты, я переносил эти записи (результаты оценки политик со временем позже, чем @StartTime) в две таблицы, которые построил ранее: в msdb.dbo.PolicyEvaluation и в msdb.dbo.PolicyEvaluation_FailureDetails.

Компания-заказчик выразила желание производить запуск сценария оценки политик каждое воскресенье в одно и то же время. Поэтому я организовал расписание Verify_Policies_Schedule (листинг 6) и привязал его ко всем созданным политикам.

После изменения режима оценки всех политик на On schedule и привязывания их к расписанию Verify_Policies_Schedule я настроил задание Verify_Policies_Job. Оно состоит из двух шагов.

1. Просмотр хранилища политик на каждом сервере и проверка всех запланированных политик путем запуска команды Windows PowerShell:

Invoke-PolicyEvaluation

Подробности описания этой команды можно найти в статье Microsoft «Using the Invoke-PolicyEvaluation cmdlet» (по адресу msdn.microsoft.com/en-us/library/cc645987.aspx).

2. Исправление нарушений (если возможно), с использованием хранимой процедуры dbo.ApplyFix, которая представлена на листинге 3. В листинге 7 показан сценарий, создающий задание Verify_Policies_Job. С этим сценарием связано несколько проблем.

Он не гибкий — одинаковые задания Verify_Policies_Job должны быть выполнены на каждом из экземпляров SQL Server 2008. В предложенном варианте в момент, когда команда инженеров создает новый экземпляр SQL Server, им необходима гибкость в поиске соответствующего локального хранилища политик.
Универсальный идентификатор (UID) расписания Verify_Policies_Schedule жестко закодирован (он указывает на Verify_Policies_Schedule); однако каждый раз, когда администратор базы данных устанавливает сценарии на другой сервер, это должен быть другой UID. Следует повысить гибкость путем оценки политики, связанной с именем расписания (Verify_Policies_Schedule), а не с его UID, сгенерированного системой.

Нет никаких затруднений, связанных с динамическим переопределением содержания шага T-SQL внутри задания. К сожалению, шаг задания, который основан на команде PowerShell, должен быть проверен до первого шага, с которого начинается задание.

Мне потребовалось другое задание, которое правильно настраивает шаг 1 задания Verify_Policies_Job и вызывает это задание с новыми, верно определенными параметрами. Листинг 8 представляет сценарий, который генерирует другое задание оценки политики Configure_Verify_Policies.

Это задание подготавливает корректное содержание шагов для следующего задания Verify_Policies_Job и сразу начинает его выполнение. Кроме того, нам бы хотелось быть уверенными, что в тот момент, когда мы запускаем задание Configure_Verify_Policies, уже не выполняется никаких других экземпляров этого задания Verify_Policies_Job. В противном случае нам, возможно, не удастся однозначно распознать результаты последних оценок политик в системных таблицах.

Однако вместо того, чтобы создавать новое, я решил использовать уже существующее фиктивное задание. В первый раз, когда пользователь устанавливает оценочный режим любой политики в On schedule, SQL Server по умолчанию создает такое фиктивное задание с названием, которое начинается префиксом syspolicy_check_schedule_. В то же время вы не можете удалить это задание до тех пор, пока в системе остается хотя бы одна запланированная политика. Таким образом, вместо заботы о присутствии бесполезного задания в системе я переименовал его в Configure_Verify_Policies.

Я удалил все шаги, первоначально сгенерированные SQL Server, и динамически переопределил все шаги в этом задании. Задание Configure_Verify_Policies состоит из трех шагов, которые вы можете проследить в листинге 8. Итак, нам нужно сделать следующее.

1. Удалить работающее задание. На этом шаге я определяю и выполняю динамический T-SQL, который проверяет, есть ли работающий экземпляр задания Verify_Policies и, если нужно, удаляет это задание.

2. Настроить ScheduleUID. На данном шаге я произвожу несколько действий:

нахожу универсальный идентификатор UID для Verify_Policies_Schedule;
нахожу фактический сервер и имя экземпляра;
заканчиваю построение динамического запроса SQL для параметра @command хранимой процедуры msdb.dbo.sp_update_jobstep и выполняю ее; в результате изменяется информационное наполнение сценария PowerShell, который должен быть выполнен на шаге 1 задания Verify_Policies;
определяю местоположение файла регистрации задания; заказчик просил, чтобы я обеспечил администратора базы данных журналом регистрации для отслеживания шагов выполнения хранимой процедуры ApplyFix в отладочных целях; мы решили размещать файл журнала задания в ту же самую папку, где находится файл журнала агента SQL Server Agent;
используя изложенную выше технику, мы настраиваем и выполняем динамический SQL, чтобы переназначить вывод данных шага 2, задания Verify_Policies, в файл журнала, находящийся в соответствующем месте.

3. Выполнить задание Verify_Policies_Job. На этом шаге мы действительно исполняем усовершенствованное задание Verify_Policies_Job с запуска хранимой процедуры sp_start_job. В конце кода сценария я связываю задание Configure_Verify_Policies с расписанием Verify_Policies_Schedule.

Чистка результатов оценки политики

Как уже отмечалось, результаты оценки каждой политики (запущенной по расписанию или по требованию из SQL Server Management Studio, SSMS) сохраняются в системных таблицах. Кроме того, SQL Server 2008 устанавливает предварительно настроенное системное задание syspolicy_purge_history, предназначенное для очистки этих таблиц. Задание должно периодически запускаться для удаления устаревших данных из таблиц dbo.syspolicy_policy_execution_history и dbo.syspolicy_policy_execution_history_details базы данных msdb.

По умолчанию задание настроено на сохранение всех данных. Соответственно листинг 9 следует запускать для активизации задания syspolicy_purge_history и очистки результатов оценки политик после 15 дней хранения.

Вы можете использовать SSMS для подмены предварительно заданного расписания запусков задания, как и продолжительности интервала хранения, что показано на экране 4. Для изменения продолжительности периода хранения «истории проверок» перейдите в меню на Management, Policy Management и щелкните правой кнопкой мыши на пункте Properties, а затем измените величину параметра HistoryRetentionInDays.

Экран 4. Окно Policy Management Properties

Управление решением

Простейший путь включить или исключить политику из процесса проверок — это назначить ей режим Enabling (активировать) или Disabling (заблокировать). Используйте SSMS для перехода в меню Management, Policy Management, Policies и выберите конкретную политику, а затем правой кнопкой щелкните Enable или Disable, в зависимости от ситуации.

Для добавления исключений в процесс проверки политики вам необходимо добавить по меньшей мере одну запись к таблице dbo.PolicyConfiguration и использовать значение 2 в столбце IncludeFlag. В некоторых случаях можно определить и дополнительные элементы для включения в процесс проверки. Для них следует использовать значение 1 в столбце IncludeFlag. Например, для включения триггера DDL в процесс проверки примените следующую команду:

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

Как обсуждалось выше, политики могут запускаться либо в режиме Display Only, либо в Enforce Policy. Для изменения режима проверки политики необходимо установить соответствующее значение в столбец EvaluationMode таблицы msdb.dbo.PolicyExecution. Например, для запуска политики Database DDL Triggers Enabled в режиме Display Only, используйте следующую команду:

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

Чтобы просмотреть результаты исполнения политики, выполните команды:

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

Предыдущие результаты выполнения политики в этих двух таблицах усечены в начале каждого запуска задания Configure_Verify_Policies. Содержимое обеих таблиц обновляется при каждом получении результатов проверки политик.

Успешно отработавшая политика будет иметь значение 1 в столбце SuccessFlag таблицы msdb.dbo.PolicyEvaluation. Нарушения политик перечисляются в таблице msdb.dbo.PolicyEvaluation_FailureDetails.

Если политика выполняется в режиме Enforce Policy, то результаты «принудительного лечения» политики попадут в столбец FixFlag. Положительные значения в этом столбце будут означать успешное исправление отклонений в политике.

Каждый раз при выполнении задания Configure_Verify_Policies генерируется файл журнала. Он называется Verify_Policies.txt и размещается в той же папке, где хранятся файлы журналов SQL Server Agent (например, «C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2 K8\MSSQL\Log»).

Необходимо иметь соответствующие права на просмотр журнала. Дополнительно вы можете настроить процесс отправки уведомления на случай, если процесс проверки найдет нарушения политики при анализе значений столбца SuccessFlag в таблице msdb.dbo.PolicyEvaluation.

Устранение ограничений управления SQL Server 2008 на основе политик

Предложенное решение обеспечивает администратора базы данных инструментом, который одновременно реализует общие политики на всех управляемых экземплярах SQL Server и позволяет администраторам баз данных вводить исключения, если у приложения были особые требования. Наконец, но не в последнюю очередь техническая группа приобрела методологию для развертывания существующих наборов политик.

Гари Зайка (garyzaik@microsoft.com) — ведущий консультант Microsoft Consulting Services в Нью-Йорке. Кандидат математических наук, имеет звания MCDBA и MCSD. Работает с SQL Server около 13 лет

Листинг 1. Создание таблицы PolicyConfiguration

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

Листинг 2. Создание таблицы PolicyExecution

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

Листинг 3. Создание таблицы PolicyEvaluation и PolicyEvaluation_FailureDetails

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

Листинг 4. Выражение для определения количества созданных пользователями активных триггеров DDL в базе данных

SELECT COUNT(*)
FROM
 sys.triggers 
WHERE
 is_disabled = 1
AND is_ms_shipped = 0 
AND parent_class_desc = ‘DATABASE’ 
AND name IN
  (SELECT Target FROM
 msdb.dbo.PolicyConfiguration WHERE
 EvalPolicy = 'Database DDL Triggers
 Enabled' AND IncludeFlag = 1)

Листинг 5. Управление на основе политики с применением функции ExecuteSQL и предложения SELECT

ExecuteSQL ('Numeric', 'SELECT COUNT(*)
FROM
 sys.triggers 
WHERE
 is_disabled = 1 
AND is_ms_shipped = 0 
AND parent_class_desc = 'DATABASE' 
AND name IN (SELECT Target FROM
 msdb.dbo.PolicyConfiguration WHERE
 EvalPolicy = 'Database DDL
 Triggers Enabled' AND IncludeFlag = 1)‘)

Листинг 6. Генерируем расписание проверки политик Verify_Policies_Schedule

PRINT  'CREATING SCHEDULE FOR POLICY EVALUATION'
BEGIN TRANSACTION
BEGIN TRY
DECLARE
@ReturnCode int,
@schedule_uid uniqueidentifier,
@schedule_id int,
@pol_id int
SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules_localserver_view
WHERE name = N'Verify_Policies_Schedule'
IF  @schedule_uid IS NULL
BEGIN
  SELECT @ReturnCode = 0
  EXEC @ReturnCode =  msdb.dbo.sp_add_schedule
       @schedule_name = N'Verify_Policies_Schedule'
      ,@enabled = 1
      ,@freq_type = 8    --weekly
      ,@freq_interval = 1    --Sunday
      ,@freq_subday_type = 1    --at the specific time
      ,@freq_subday_interval = 0          
      ,@freq_relative_interval = 0
      ,@freq_recurrence_factor = 1    --once a week
      ,@active_start_date = 20080101
      ,@active_end_date = 99991231
      ,@active_start_time = 500         --00:05:00
      ,@active_end_time = 235959
      ,@owner_login_name = 'sa'
      ,@schedule_uid = @schedule_uid OUTPUT
      ,@schedule_id = @schedule_id OUTPUT
                    
  IF (@ReturnCode <> 0)
    BEGIN
      PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'
      GOTO QuitWithRollback
    END
END   
SELECT @pol_id = policy_id FROM msdb.dbo.syspolicy_policies
WHERE name = N'Database DDL Triggers Enabled'
IF @pol_id IS NOT NULL
  EXEC msdb.dbo.sp_syspolicy_update_policy
     @policy_id = @pol_id,
     @execution_mode = 4,
     @is_enabled = True,
     @schedule_uid = @schedule_uid
/* Здесь следует вставить аналогичный код для других политик */
COMMIT TRAN
PRINT  'SUCCESSFULLY CHANGED EVALUATION MODE FOR ALL POLICIES TO On Schedule'
END TRY
BEGIN CATCH
  PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'
  PRINT  ‘’
  SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH    
QuitWithRollback:   
GO

Листинг 7. Код, создающий задание на проверку политик Verify Policies Job

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

Листинг 8. Сценарий, создающий задание Configure Verify Policies

USE [msdb]
GO
BEGIN TRAN
DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier,
  @schedule_id int, @dummy_job_id uniqueidentifier
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories
               WHERE name=N’[Uncategorized (Local)]’ AND category_class=1)
BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’,
       @type=N’LOCAL’, @name=N’[Uncategorized (Local)]’
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules_localserver_view
       WHERE name = N’Verify_Policies_Schedule’
SELECT @dummy_job_id = job_id FROM msdb.dbo.sysjobs_view
       WHERE name = ‘syspolicy_check_schedule_’ + CONVERT(char(36),@schedule_uid)
IF @dummy_job_id IS NOT NULL
BEGIN
  EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@dummy_job_id,
                      @new_name = N’Configure_Verify_Policies’,
                      @enabled=1,
                      @notify_level_eventlog=0,
                      @notify_level_email=0,
                      @notify_level_netsend=0,
                      @notify_level_page=0,
                      @delete_level=0,
                      @description=N’No description available.’,
                      @category_name=N’[Uncategorized (Local)]’,
                      @owner_login_name=N’sa’
                    
  SET @jobId=@dummy_job_id
END
ELSE
BEGIN
  SELECT @jobId = job_id FROM msdb.dbo.sysjobs_view
         WHERE name = N’Configure_Verify_Policies’
  IF @jobId IS NOT NULL
    EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@jobId,
                        @enabled=1,
                        @notify_level_eventlog=0,
                        @notify_level_email=0,
                        @notify_level_netsend=0,
                        @notify_level_page=0,
                        @delete_level=0,
                        @description=N’No description available.’,
                        @category_name=N’[Uncategorized (Local)]’,
                        @owner_login_name=N’sa’
  ELSE
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name= N’Configure_Verify_Policies’,
                        @enabled=1,
                        @notify_level_eventlog=0,
                        @notify_level_email=0,
                        @notify_level_netsend=0,
                        @notify_level_page=0,
                        @delete_level=0,
                        @description=N’No description available.’,
                        @category_name=N’[Uncategorized (Local)]’,
                        @owner_login_name=N’sa’,
                        @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers WHERE job_id = @jobId)
BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
       @job_name = N’Configure_Verify_Policies’,
       @server_name = @@ServerName
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
END
DECLARE @steps int, @i int
SELECT @steps = COUNT(*) FROM dbo.sysjobsteps s, dbo.sysjobs j
       WHERE s.job_id = j.job_id AND j.name = N’Configure_Verify_Policies’
SELECT @i = @steps
WHILE (@i > 0)
BEGIN 
  EXEC dbo.sp_delete_jobstep
       @job_name = N’Configure_Verify_Policies’,
       @step_id = @i ;
  SET @i = @i - 1
END
/******   ШАГ: [Удалить выполняющееся задание] Step [Kill Running Job] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  @step_name=N'Kill Running Job',
             @step_id=1,
             @cmdexec_success_code=0,
             @on_success_action=4,
             @on_success_step_id=2,
             @on_fail_action=2,
             @on_fail_step_id=0,
             @retry_attempts=0,
             @retry_interval=0,
             @os_run_priority=0, @subsystem=N'TSQL',
             @command=N'
IF EXISTS (SELECT j.[name]
           FROM msdb.dbo.sysjobactivity a
           JOIN msdb.dbo.sysjobs j
           ON j.job_id=a.job_id and j.name = ''Verify_Policies''
           WHERE a.start_execution_date IS NOT NULL
           AND a.job_history_id IS NULL)
   EXEC msdb.dbo.sp_stop_job N''Verify_Policies''
              ',
              @database_name=N'msdb',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
/******  ШАГ: [Сконфигурировать Schedule UID] ******/
DECLARE @StepString  nvarchar(max)
SET @StepString = N'DECLARE    @TargetSrv    sysname,
        @ScheduleUid uniqueidentifier, @sScheduleUid varchar (40),
        @CommandString nvarchar(max)
DECLARE @DataLocation varchar(1000), @OutputFile varchar(1000)
EXEC master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''Software\Microsoft\MSSQLServer\SQLServerAgent'', N''ErrorLogFile'',
  @DataLocation OUTPUT
SELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX(''\'',REVERSE(@DataLocation)))
SET           @OutputFile          = @DataLocation + ''\Verify_Policies.log''
SELECT @TargetSrv = @@ServerName
SELECT @ScheduleUid = schedule_uid FROM msdb.dbo.sysschedules_localserver_view
       WHERE name = ''Verify_Policies_Schedule''
SELECT @sScheduleUid= CONVERT(nchar(36),@ScheduleUid)
IF CHARINDEX(''\'',@TargetSrv) > 0
  SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  @TargetSrv + ''\Policies | where { $_.ScheduleUid -eq "'' +
  @sScheduleUid + ''" } |  where { $_.Enabled -eq 1} | where
  {$_.AutomatedPolicyEvaluationMode -eq 4} | Invoke-PolicyEvaluation
  -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
ELSE
  SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  @TargetSrv + ''\DEFAULT\Policies | where { $_.ScheduleUid -eq "'' +
  @sScheduleUid + ''" } |  where { $_.Enabled -eq 1} | where
  {$_.AutomatedPolicyEvaluationMode -eq 4} | Invoke-PolicyEvaluation
  -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
EXEC msdb.dbo.sp_update_jobstep
       @job_name = ''Verify_Policies''
       ,@step_id = 1,@command = @CommandString
SELECT @CommandString = N''EXEC dbo.ApplyPolicies '''''' +
       CONVERT(varchar(30),getdate(),120) + '''''',1''
EXEC msdb.dbo.sp_update_jobstep
       @job_name = ''Verify_Policies'',@step_id = 2,@output_file_name=@OutputFile,   
       @command = @CommandString        '
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
              @step_name= N'Configure ScheduleUid',
              @step_id=2,
              @cmdexec_success_code=0,
              @on_success_action=4,
              @on_success_step_id=3,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=@StepString,
              @database_name=N'msdb',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
/******   ШАГ: [Выполнить задание Verify_Policies] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
              @step_name=N'Run Verify_Policies Job',
              @step_id=3,
              @cmdexec_success_code=0,
              @on_success_action=1,
              @on_success_step_id=0,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=N'EXEC dbo.sp_start_job N''Verify_Policies'' ',
              @database_name=N'msdb',
              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
   @job_name = N'Configure_Verify_Policies',
   @schedule_name = N'Verify_Policies_Schedule' ;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
PRINT  'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
BEGIN
   ROLLBACK TRANSACTION
   PRINT  'COULD NOT CREATE JOB Configure_Verify_Policies'
END
EndSave:

Листинг 9. Код очистки таблиц от записей результатов проверки политик «15-дневной давности»

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