SQL Server PBM: On Schedule Failures

I tend to like the automation and convenience of using Policy Based Management (PBM) to check simple things like my backups have been running, there’s enough disk space on my servers, and the consistency of my configurations (min/max server memory, ad hoc workloads, etc.).

While it is easy to build and test policies by executing them on demand (especially powerful when run through Central Management Server) I had some issues getting my policies to run in “on schedule” mode.

To be more specific, my policies that use the ExecuteSQL function have been an issue.  What I was finding was:

  • The policy would run fine “on demand” but…
  • When I run the policy through the PBM scheduler, the policy would fail.

Dealing with false positives is not a good start for any monitoring service, so getting to the root of the issue was critical.

It turns out that when you run a policy “on demand” the policy runs with the permissions of the person running the policy.  But when the policy is run “on schedule” it uses a special, preconfigured proxy login called ##MS_PolicyTsqlExecutionLogin##.  Now I have to admit, I’ve seen these logins but never gave them a second thought.

So the next time your “on schedule” policies are giving false positives, the first place to look is at the permissions that the proxy login has.  By default it is basically a member of PUBLIC and has no real permissions.  In order to get the policies to run correctly, you will need to assign the minimum permissions to the proxy login to do the work that you are asking the PBM to do.

It’s interesting to note that if the proxy does not have the correct permissions to run the policy, it will not throw a permissions related error.  It will just show the policy execution as a failure, which results in an error 34052 being written to the event log, indicating a failed PBM.

In my opinion, there should be a distinction between a failed policy, and a policy that fails to run due to permissions issues or syntax errors, or anything other than the policy check itself.

Stay curious; keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>