SQL Server Policy Based Management – Introduction

The estimated reading time for this post is 5 minutes

SQL Server 2008 introduced Policy Based Management feature to ease the managing and monitoring SQL Server instances. The Policy-Based Management feature assists you to monitor and enforce rules for the SQL Server Database Engine. The policies can be applied to one or more SQL Server instances. The Policy based management provides a multi-instance management capability to protect the integrity of the SQL Server environment through the organizations.

Policy Based Management Concepts

The PBM includes these key concepts:

  • Facets. A pre-defined set of logical properties of SQL Server configuration. The following query shows all available facets:

                SELECT name FROM msdb.dbo.syspolicy_management_facets

  • Conditions. A Boolean expression that is evaluated against facet properties.
  • Policies. A standard you define against one or more facets through a condition.
  • Evaluation modes. Dictates how a policy will be evaluated against one or more targets. The facet determines evaluation mode within the condition. SQL Server supports these four evaluation modes:
    • On Demand: a manual evaluation by DBA.
    • On Schedule: the policy will be evaluated at the defined schedule.
    • On ChangeLog Only: the result of the policy violation is captured in the SQL Server log.
    • On ChangePrevent: it prevents out-of-compliant changes that violates the policy from taking place.
  • Targets. A place which a policy is enforced. This could either be a database or a table or entire SQL instance.
  • Categories. To group the policies against specified target.

SQL Server Policy Based Management Best practices

Microsoft provides pre-defined policies based on best standards with SQL Server installation. By default, these policies are present in the following path:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Policies\DatabaseEngine\1033

Import Policy Based Management Best Practices

To use these pre-defined policies inside SSMS, follow these steps

  • Navigate to the Management node in Object Explorer and expand it.
  • Expand Policy Management node.
  • Right-click Policies, and select Import Policy…

image-3904

  • Click on (…) and navigate to the files path as mentioned before. You can choose all files or specific file per your requirement.
  • You can change the policy state to enable or disable or preserve the policy state on import
  • Once you click on OK policy/policies will be present under Policies node.

image-3905

Create Policy

Above policies are in common for all SQL Server environments. Sometimes your company has its own policies that need to implement and monitor. For example, based on company policies, no login should start with number, but you know that you are able to create logins which start with numbers. So, let’s start with the policy ensures that no logins start with numbers.

Create a New Condition

In the first step, we need to create a new condition to meet our requirement. In SSMS, expand Policy Management under Management and right-click Conditions, click New Condition.

image-3906

Choose a proper name for the condition. We call it “LoginNameCheck”. Select the facet “Login Options”.

Bear in mind that if you choose “Logins” for facet, “Logins” facet does not support “On change” evaluation modes. As result, you only have “On Demand” and “On Schedule” evaluation modes. [\info]

In expression grid we need to define our logical expression. We use @Name as field with “NOT LIKE” operator and value ‘[0-9]%’. This means there should be no login with number in the start of its name.

image-3907

Create a new Policy

Next, we need to create a new policy. In SSMS, expand Policy Management under Management and right-click Policies, click New Policy. We name it “LoginNamePolicy”. In “Check condition” dropdown, select the condition “LoginNameCheck”, which we created in previous step.

image-3908

Our goal is to enforce this policy against every login. So, we leave the “Against targets” to “Every login”. Though, you can change the “Every” to “New condition” to create your own condition to exclude logins that you want to exempt from this policy.

In “Evaluation Mode” dropdown box, we change it to “On change: prevent”, to prevent an action of creating a login that violate our policy. Just make sure you tick the enabled button. Otherwise, the policy will be created in disabled mode and does not prevent any violation.

We leave the “Server restriction” to stay “None”. In description tab you can add additional help to make it clearer for users.

To check whether our policy is working or not, we are going to create a login starts with a number. And we encounter this error:

image-3909

Evaluate a policy

Now we have a policy ready to be evaluated against the target. For the above example, we can trace what violations happened and also if users might create logins that violate the policy prior to creating the policy. Those policies that are “On-Demand” as the evaluation mode, the only way the policy can be checked is by manually evaluating it. It is done by right-clicking the mentioned policy and clicking “Evaluate”.

image-3910

As I created a login which is started with number (here is the login name as ‘123’) before creating the policy, the evaluation returns a failure message for the check against this login.

image-3911

To see the evaluation history, go back to the policy, right-click policies and select “View History”.

  

image-3912

failure icon in the evaluation history demonstrate the violations against this policy. To see what all the violations were, Click the hyperlink in the Details column to see the “Results Detailed View” window.  Here, condition failed in the case of “123” login i.e. the login’s name start with a number. The ‘Expect Value’ and ‘Actual Value’ columns show what was expected and what was actually the case.

image-3913

Export and Import a Policy

Sometimes you need to implement the same policies in multiple servers. SQL Server allows you to export a policy to XML and import it to the other server. In our case, we will export the policy that we created in previous step to an XML file and then import it into another server.

Export

To export a policy, right-click on a policy you want to be exported and select “Export Policy”.

image-3914

Navigate to the location that you want to save the policy as XML file and click Save to complete this step.

Import

To import a policy, login to the target SQL Server instance. Right-click the Policies under Policy Management and select Import Policy (same as what we have done to import Microsoft best practices). On the import window, click the browse button (…) to browse to the location where the policy is present and click Open.

image-3915

You can choose to overwrite the existing policy with the one being imported. Select the appropriate Policy state and click OK. The policy will be under Policies folder.

image-3916

So far we learnt how the Policy-Based Management is a great feature to enforce standards across SQL Server infrastructure. You can leverage the Central Management Server to evaluate a policy against multiple SQL instances at the same time in one go as opposed to checking it one-by-one. 

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website


Leave a Comment

avatar
1000
  Subscribe  
Notify of