Low-Privilege Environments

Applies To: Operations Manager 2007, Operations Manager 2007 R2, SQL Server

This section describes how to configure the SQL Server management pack for low-privilege access (discovery, monitoring, and action) for both monitored SQL Server instances and the host operating system. When you follow the instructions in this section, the health service hosting the SQL Server management pack executes all the workflows with low-privilege access to the target SQL Server instances and to the operating system where those instances are running.

For more information, see the Run As Profiles section.

Note

Low privilege for SQL Server Mirroring is not supported.

Setting up a Low-Privilege Environment

Note

The following procedure describes the steps needed to configure low-privilege Discovery, Monitoring, and Action for version 6.4.0.0 of the SQL Server Management Pack. This low-privilege configuration is only supported for non-clustered SQL Server environments. .

Clustered SQL Server instance monitoring under the Low-Privilege is supported only for SQL Server 2012 monitored from System Center Operations Manager 2012. It is not guaranteed to work for previously-released management packs.

To configure the low-privilege environment in Active Directory

  1. In Active Directory, create three domain users that will be commonly used for low-privilege access to all target SQL Server instances:

    1. SQLDefaultAction

    2. SQLDiscovery

    3. SQLMonitor

  2. Create a domain group named SQLMPLowPriv and add the following domain users:

    1. SQLDiscovery

    2. SQLMonitor

  3. Grant special permission: Read-only Domain Controllers - “Read Permission” to the SQLMPLowPriv.

To configure the low-privilege environment on the agent machine

  1. On the agent machine, add the SQLDefaultAction and SQLMonitor domain users to the “Performance Monitor Users” local group.

  2. If the operating system of the agent machine is Windows Server 2008 or Windows Server 2008 R2, add the SQLDefaultAction and SQLMonitor domain users to “EventLogReaders” local group.

  3. Add the SQLDefaultAction domain user and SQLMPLowPriv domain group as members to the local Users group.

  4. Configure the “Log On Locally” local security policy setting to allow the SQLDefaultAction domain user and SQLMPLowPriv domain group users to log on locally.

  5. Grant Read permission on HKLM:\Software\Microsoft\Microsoft SQL Server registry path for SQLDefaultAction and SQLMPLowPriv.

  6. Grant “Execute Methods”, “Enable Account”, “Remote Enable”, “Read Security” permissions for root, root\cimv2, root\default, root\Microsoft\SqlServer\ComputerManagement11 WMI namespaces to SQLDefaultAction and SQLMPLowPriv.

  7. Grant Read permission on HKLM:\Software\Microsoft\Microsoft SQL Server\[InstanceID]\MSSQLServer\Parameters registry path for SQLMPLowPriv for each monitored instance.

To configure the low-privilege environment on the agent machine in cluster

  1. Grant “Remote Launch” and “Remote Activation” DCOM permissions to the SQLMPLowPriv, SQLDefaultAction using DCOMCNFG.

  2. Allow Windows Remote Management through the Windows Firewall.

  3. Grant “Read” and “Full Control” access for the cluster to the SQLMPLowPriv using Failover Cluster Manager.

To configure the instances for monitoring in SQL Server Management Studio

  1. In SQL Server Management Studio, create a login for “SQLMPLowPriv” on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each “SQLMPLowPriv” login:

    1. VIEW ANY DEFINITION

    2. VIEW SERVER STATE

    3. VIEW ANY DATABASE

  2. Create a “SQLMPLowPriv” user that maps to the “SQLMPLowPriv” login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a “SQLMPLowPriv” user in each future user-created database. See the code sample below. You will need to manually provision the user for attached and restored databases.

  3. Add the SQLMPLowPriv user on msdb to the “SQLAgentReaderRole” database role.

  4. Add the SQLMPLowPriv user on msdb to the “PolicyAdministratorRole” database role.

To configure the instances for default action in SQL Server Management Studio

  1. In SQL Server Management Studio, create a login for SQLDefaultAction on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each SQLDefaultAction login:

    1. VIEW ANY DEFINITION

    2. VIEW SERVER STATE

    3. VIEW ANY DATABASE

  2. Create a SQLDefaultAction user that maps to the SQLDefaultAction login in each existing user database, master, msdb, and model. By putting the user in to the model database, you automatically create a SQLDefaultAction user in each future user-created database. See code sample below. You need to manually provision the user for attached and restored databases.

  3. Add a SQLDefaultAction user on msdb to the “SQLAgentReaderRole” database role.

  4. Add the SQLDefaultAction user on msdb to the “PolicyAdministratorRole” database role.

Some optional System Center Operations Manager tasks require a higher privilege on the agent machine and the databases where the tasks need to be executed. You should only execute the following provisioning steps on the agent machine or databases where you want the System Center Operations Manager console operator to take remedial actions.

To enable execution of System Center Operations Manager tasks for a database object

  1. On the agent machine, grant the SQLDefaultAction user permission to start or stop a NT service if the task is about starting or stopping a NT service such as DB Engine Service, SQL Server Agent service, SQL FullText Search Service, Analysis Services, Integration Services, and Reporting Services. This involves setting a service’s security descriptor. For more information, see Sc sdset.

    The basic process is to read the existing privileges for a given service (using sc sdshow) and then grant additional privileges to the SQLDefaultAction user for that server.  For example, suppose the results of the SC sdshow command for SQL Server service are as follows:

    D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

    In that case, the following command line confers sufficient access to SQLDefaultAction for starting and stopping the SQL Server service (with appropriate substitutions for italicized values and keeping everything on a single line of text):

    sc sdset SQL Server service name D:(A;;GRRPWP;;;SID for SQLDefaultAction)(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

  2. In SQL Server Management Studio, add “SQLDefaultAction” to db_owner database role for each database to check:

    1. “Check Catalog (DBCC)”

    2. “Check Database (DBCC)”

    3. “Check Disk (DBCC)” (invokes DBCC CHECKALLOC)

  3. Grant the ALTER privilege to SQLDefaultAction for each database on which to set state:

    1. “Set Database Offline”

    2. “Set Database Emergency State”

  4. Grant the ALTER ANY DATABASE privilege to SQLDefaultAction login to run the task of “Set Database Online”.

To configure System Center Operations Manager

  1. Import the SQL Server Management Pack if it has not been imported.

  2. Create a SQLDefaultAction, SQLDiscovery and SQLMonitor Run As account with “Windows” account type. For more information about how to create a Run As account, see How to Create a Run As Account in Operations Manager 2007. For more information about various Run As Account types, see Run As Accounts and Run As Profiles in Operations Manager 2007 or Managing Run As Accounts and Profiles

  3. On the System Center Operations Manager console, configure the Run As profiles for the SQL Server Management Pack as following:

    1. Set the “Default Action Account” Run As profile to use the SQLDefaultAction Run As account.

      Warning

      Note: When you make SQLDefaultAction the “SQL Server Default Action Account” at the management server, you need to grant access to the Operations Manager database.

      1. Grant CONNECT to SQLDefaultAction for the OperationsManager database.

      2. Add “SQLDefaultAction” to the dbmodule_users database role.

    2. Set the “SQL Server Discovery Account” Run As profile to use the SQLDiscovery Run As account.

    3. Set the “SQL Server Monitoring Account” Run As profile to use the SQLMonitor Run As account.

The following code example shows provisioning the SQLMPLowPriv login on an instance.

use master
go

create login [yourdomain\SQLMPLowPriv] from windows
go

grant view server state to   [yourdomain\SQLMPLowPriv]
grant view any definition to [yourdomain\SQLMPLowPriv]
grant view any database to [yourdomain\SQLMPLowPriv]
grant select on sys.database_mirroring_witnesses to [yourdomain\SQLMPLowPriv]
go

The following code example shows how to generate a Transact-SQL provisioning script. The generated script provisions the SQLMPLowPriv user in current user databases and also the model database (thereby automating the provisioning in future databases).

Warning

Note: You need to output the results of this query in text format.

SELECT 'use ' + name + ' ;' 
+ char(13) + char(10)
+ 'create user [yourdomain\SQLMPLowPriv] FROM login [yourdomain\SQLMPLowPriv];'
+ char(13) + char(10) + 'go' + char(13) + char(10) 
FROM sys.databases WHERE database_id = 1 OR database_id >= 3
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername=''yourdomain\SQLMPLowPriv''' 
+ char(13) + char(10) + 'go' + char(13) + char(10)
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername=''yourdomain\SQLMPLowPriv''' 
+ char(13) + char(10) + 'go' + char(13) + char(10)