locked
SQL Management Pack on a 2012 Cluster RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I implemented the low-privilege setup from the SQL MP guide because on a few standalone SQL instances and one SQL 2012 cluster, not all scripts were running.  The SQL cluster is a two node cluster with 2 SQL instances in and active/active config (One instance is active on each node in the cluster).  On the standalone SQL instances, the low-privilege setup is working great.  On the SQL cluster it is not.  Reading the latest SQL MP guide, it appears that SQL 2012 cluster are supported for monitoring.  I've made the additional configuration changes that the MP guide says is required for cluster monitoring.  I am getting PowerShell script execution error messages in the OpsMgr console stating a lack of permissions.  When I look at the SQL error logs, I see that something is trying to log into the databases as NT AUTHORITY\SYSTEM and can't because access is denied.  That login is not mapped to any databases - obviously.  Why would these workflows be trying to execute as NT AUTHORITY\SYSTEM and not one of the the action accounts in the SQL RunAs profiles that I've setup and deployed to the two cluster nodes? 

    Monday, June 16, 2014 10:10 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Based on my research, when SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list.  The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to.  If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights.  If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA.

    When SQL 2012 is installed by default – we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access.

    I would like to suggest you recheck your configuration of runas profile and action account, please follow the below article:

    http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx

    Regards,

    Yan Li

    Regards, Yan Li

    Thursday, June 19, 2014 10:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yan Li,

    Thanks for writing a response.  I've gone through that article and checked my setup against it.  Kevin Holman is not using the low-privilege setup as it is specified in the SQL MP guide but I'm following the basic principles as far as deploying RunAs accounts.

    My point is that the SQL scripts don't seem to be being executed by the RunAs accounts I'm specifying.  They are being run as NT AUTHORITY\SYSTEM - or at least NT AUTHORITY\SYSTEM is part of the process of the scripts running in this cluster setup.  I’m trying to understand why the scripts aren’t being solely being executed by the RunAs accounts I deployed.  It seems that they are running with the default action account specified for the nodes (Local System) instead of the RunAs account I specified for the SQL RunAs profiles.  Is there something special that needs to be done to deploy them in a cluster situation so that they are used instead of the default action account?

    As a test, I temporarily gave NT AUTHORITY\SYSTEM sysadmin rights on the SQL instance.  I see in the OpsMgr logs on the agent that the SQL RunAs accounts successfully logged in now.  Scripts ran and monitoring of the instance is going.  There must be something else I'm missing for the SQL monitoring cluster setup using these low-privilege accounts.

    Also, I just added another stand-alone SQL instance using these low-privilege accounts and monitoring is working perfectly.  The issue is only with cluster SQL monitoring and not stand-alone SQL monitoring

    Thursday, June 19, 2014 1:18 PM