Monitoring SQL in Low Priv environments the smart way

If you have a low priv SQL environment, you’re probably well aware of the pain of configuring and managing the run-as accounts required for SQL management packs. Well, there is a much simpler way to configure the necessary permissions, without using run-as accounts. The solution is to use Service SIDs, a method that Kevin Holman first discovered and blogged about over a year ago.

Using Service SIDs is easier to manage, firstly because you don’t have to create run as accounts, but also there are no additional permissions to configure outside of SQL. With run as accounts, you have to configure registry, file and WMI permissions, but with Service SIDs this is a thing of the past!  It’s also more secure, as you’re using a local ‘account’ on each server instead of domain accounts shared across multiple SQL servers.

If, like me, you’ve never heard about Service SIDs before, this blog post on TechNet will tell you everything you need to know.

The process for using Service SIDs is very simple, and Kevin provides step by step instructions in his blog. He’s even created a Management Pack to make the initial configuration even easier! Check out his blog post to get the details.

Here are a few notes about my experience using Service SIDs to replace the run as accounts:

  • To verify the Service SID has been enabled, run this command locally on the SQL server:

sc qsidtype HealthService

  • If you have DMZ servers or other servers that can’t be remotely managed, the task to configure Service SID from Kevin’s management pack might fail. Run this command locally from an administrative command prompt on the SQL server to enable Service SID:

sc sidtype HealthService unrestricted

  • Service SIDs aren’t available in Server 2003, so if you’re unlucky enough to still be monitoring Server 2003 you’ll have to continue using run as accounts for those servers.
  • After enabling Service SID, I saw less errors in the Operations Manager event log on SQL servers. Some of the errors that disappeared were script errors related to SQL discovery, and Event ID 11052 on SQL 2014. These errors were probably due to missing permissions, which is a non-issue with Service SIDs.

Changing to Service SIDs did require a bit of work upfront. Most of the work was in cleaning up after the old run as accounts – removing permissions and group policy settings. It was definitely worthwhile though, and if you haven’t already made the switch, I highly recommend you do it!

 

Rate this post:
Share this post:

Leave a Reply