Categories
DBA

How to work with Policy Based Management (PBM)

In my previous article on policy based management Ive gone through what is policy based management and how it helps administration. Lets discuss about how to work with policy based management i.e. how to create a policy and implement it. To work with a policy we need to first create a condition and we need to use this condition in our policy. Lets discuss each of the title mentioned below

  • How to: Create a Policy-Based Management Condition
  • How to: Create a Policy-Based Management Policy
  • How to: Evaluate a Policy-Based Management Policy

Scenario : Consider you have the company standards on XP_cmdshell, that is this procedure should not be enabled as per security standards and you need to check the list of servers which enabled this procedure.

How to: Create a Policy-Based Management Condition

The first step in creating a policy is to create a condition and we need to use this condition in our policy.

  • Connect to SQL Server 2008 instance using SSMS 2008
  • Browse to InstanceName > Management > Policy Management
  • You can find three folders (condition, policies and facets). Right click on the condition and click on New Condition, a new windows will be pop-up

pbm1

pbm2

  • From the pop-up window provide the name of the condition as shown above. Select Surface Area Configuration under facet since xp_cmdshell feature can be enabled or disabled from SAC. Under the field select @XPCmdShellEnabled and select the value to false since we need to check for xp_cmdshell enabled instance.
  • Once all the details provided click on ok to create the condition.

How to: Create a Policy-Based Management Policy

We have created a condition for the audit lets create a policy to check them.

  • Go to the Policy Management folder and expand it. Right click on the Policies folder and click on New Policy

pbm3

pbm4

  • In the main page of New policy creation windows provide the name of the policy.
  • You can find the Enabled option is in disabled state, this is because that this option is not supported for On Demand evaluation mode. Ive used this mode for evaluating the policy.
  • Targets are applicable based on the condition you are creating, in this case this is not applicable. Consider you have created a condition to check naming convention in that case you will be getting table, stored procedure etc in the target. You can select those targets and policy will check on those objects.
  • As i already said Ive used On Demand evaluation mode, so Ill start this policy for auditing.
  • This is one of the additional details you can provide, suppose if you want to exclude some of the server which starts with some name. Ill left that blank.
  • Once this page is completed, click on the description in the left pane to go the next page.

pbm5

In the description page you can select a category or you can create a new category and assign this policy to that category. You can also provide description to that policy. There are two more fields shown in the dialog box (Text to display & Address), you can provide the details there so that these details will be shown when the policy is failed. After filling up all the details in both the page of policy click ok to create the policy.

How to: Evaluate a Policy-Based Management Policy

We he have created the condition and the policy, now its time to evaluate the policy. We are going to evaluate this in two cases as mentioned below

pbm6

Case 1 – XP_cmdshell Enabled
Case 2 – XP_cmdshell Disabled

 

 

Case 1 – XP_cmdshell Enabled

In this case Ive enabled Xp_cmdshell procedure for the instance hence when we evaluate the policy it should throw the error stating that the condition is failed. Right click on the policy and click on evaluate option

pbm7

pbm8

pbm9

 

From the screenshots you can find that the policy failed and thrown the error, you can click on the view button to show the results window. In that results window you can find the additional information provided, users can go to the link and check the policy.

Case 2 – XP_cmdshell Disabled

In this case Ive disabled Xp_cmdshell procedure for the instance hence when we evaluate the policy it should get succeeded. Right click on the policy and click on evaluate option.

pbm10

Now you can find that the policy evaluated successfully since we have disabled the procedure for the instance, you can also click on the view button to see more details.

Thus its easy to implement policy based management for an instance and you can schedule this to check the company policies regularly there by making the administration simpler. PBM is one of the additional feature more helpful for the DBAs to track all the company standards for that instance.

To make PBM work against SQL 2000 & SQL 2005 check the link below.

PBM on SQL2K & SQL2K5 Link1

PBM on SQL2K & SQL2K5 Link2

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*