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.) To add an exception to the policy for a particular server, database, or object, the DBA just enters the records into this table. Columns in this table store the following information:
• PolicyConfigurationID—primary key
• EvalPolicy—policy name
• Target—name of the object (database, server) that should be included or excluded from the policy
• IncludeFlag—1 (object included); 2 (object excluded)
For example, if you want to make an exception to the policy “Blank Password For SQL Logins” on ServerA, insert the following record into dbo.PolicyConfiguration:
INSERT dbo.PolicyConfiguration (EvalPolicy,
Target, IncludeFlag)
VALUES ('Blank Password For SQL Logins',
'ServerA', 2)
Each policy can be evaluated in one of two modes: Mode Value 0 stands for Display Only mode—it only evaluates the policy, and no policy enforcement occurs if a violation is found. Mode Value 1 stands for Enforce Policy mode and enforces the policy if a violation is found.
dbo.PolicyExecution table. The evaluation mode for policy execution can be set individually in the dbo.PolicyExecution table, which Listing 2 creates. In this table, columns store the following information:
• PolicyExecutionID—primary key
• EvalPolicy—policy name
• EvaluationMode—0 (display only); 1 (enforce policy)
For example, to see whether the policy “Blank Password For SQL Logins” was violated without enforcing password assignment, insert the following record into dbo.PolicyExecution:
INSERT dbo.PolicyExecution (EvalPolicy,
EvaluationMode) VALUES ('Blank Password
For SQL Logins', 0)
To immediately enforce the policy by assigning some default password, insert the following row into dbo.PolicyExecution:
INSERT dbo.PolicyExecution (EvalPolicy,
EvaluationMode)
VALUES ('Blank Password
For SQL Logins', 1)