Hi, Gaurav Sharma here,

A few months back I was reading an article about new features in  SQL Server 2008 and it was the first time I had heard anything about Policy Based Management or PBM. PBM is a new feature introduced with SQL Server 2008 which lets you create configuration policies for SQL server instances just like the group policies that we create for Windows.

Some of the features of PBM include;

  • You can create and deploy a policy across multiple servers
  • Policies can be scheduled or run them on demand
  • PBM can also en-force a rollback of a transaction that is against a defined policy
  • PBM works on SQL Server 2000 and 2005 as well as 2008 !
  • There is an API that developers can use for writing applications that are related to PBM

Why do we need this new feature?

Newer versions of software usually introduce new features and in my experience there is a high probability that users might not know about those new features. I am just being pragmatic! The SQL Server product is great example and I suspect in SQL Server there are many features unknown to a large (the majority) percentage of users. This means that we are largely unaware of the attack surface and so we need a way to manage these features to minimize the attack surface.

Auditing servers for security also poses a real challenge. Suppose we have 10 SQL servers and we want to review each one of them for security. Our review process will likely look like this;

  1. Security review against our defined / documented standards (read standards as a checklist and compare the configuration against the checklist)  
  2. Coding standard review. e.g every table name should start with ‘tbl_’

We have probably got four options on our hands;

    1. Check the servers once and mark them pass or fail. I call this the snapshot approach.
    2. Check the servers once and mark them passed or fail and then go back and revalidate that nothing has changed on a periodic basis.
    3. Assign a resource (IT guy or DBA) to monitor validation status on daily basis.
    4. Create a SQL Job and schedule it as per our requirement.

1 or 4 are the most common approaches in my experience.

Each of above points has something negative that we definitely wish to avoid:

    1. [If we go with first choice] After our review is completed, if someone modifies our servers then we are in big soup
    2. [If we go with second choice] Today we have ten servers, but who knows how many more servers we will have in future, 100, 1000 or more. Who will take care of this? 
    3. [If we go with third choice] This is not at all a good solution, considering the costs involved. As we say around here “it doesn’t scale” 
    4. [If we go with fourth choice] Creating a scheduled jobs may seem like an easy thing good thing to do, but ensuring our job doesn’t interfere with any other DB object is a lot harder than it may seem.

If we keep security requirements as constant, still we can have lot of complex prerequisites as per our application requirement. Different kind of applications require different kind of checks (or policies). For example, security requirements for a server with High Business Impact (HBI) data are very different from the security requirements that Low Business Impact (LBI) data server has. Managing this complexity is again a huge and complex task.

SQL Server’s Policy Based Management provides us with an elegant solution.

Policy management is available under Management section. Below is a screen capture that shows PBM with its different components.

image

A teaser in the Components of PBM

  • Target – Entities that we want to manage. For example, Server instance, database, tables etc.
  • Facet – Properties associated with a target. For example properties associated with Server are Server default configuration, server security configuration etc. Facets can also model target behaviors. Like CollationMatchesModelOrMaster is a behavior of the Database.
  • Condition - Conditions that we need to evaluate. These conditions can be like Is Table Name Starting from 'TBL_'.
  • Policy - Binds together condition with expected behavior.

I’ll keep the PBM working details very short for now as there is lot of content available online related to PBM usage (check reference section for links). At Microsoft, the ACE team are responsible for carrying out deployment reviews which also involves reviewing SQL Servers with some pre defined checklist items. A few weeks back, I started modeling these checklist items as PBM’s policies and completed the task successfully.

In my next post I’ll focus on these security policies and how I used PBM to audit checking against them. I am told it saves hours and hours of time and frustration!

References

Policy Based Management – Team Blog
How to use PBM – MSSQLTIPS
PBM Working – Database Journal