In my previous article on policy based management I’ve 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
-
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
-
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. I’ve 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 I’ve used “On Demand” evaluation mode, so I’ll 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. I’ll left that blank.
-
Once this page is completed, click on the description in the left pane to go the next page.
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
- Case 1 – XP_cmdshell Enabled
- Case 2 – XP_cmdshell Disabled
Case 1 – XP_cmdshell Enabled
In this case I’ve 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
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 I’ve 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.
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 it’s 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 DBA’s to track all the company standards for that instance.
To make PBM work against SQL 2000 & SQL 2005 check the link below.
Comments are closed.