Low-Privilege Environments

Updated: August 2, 2010

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.

Setting up a Low-Privilege Environment

noteNote
The following procedure describes the steps needed to configure low-privilege Discovery, Monitoring, and Action for version 6.1.400.00 of the SQL Server Management Pack. This low-privilege configuration is only supported for non-clustered SQL Server environments. 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

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.

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

  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.

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

  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.

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 2007

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

  2. Create a SQLDefaultAction Run As account with “Action Account” account type for the SQLDefaultAction domain user; then create SQLDiscovery and SQLMonitor Run As accounts with “Windows” account type for SQLDiscovery domain user and SQLMonitor domain user. 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.

  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.

      WarningWarning
      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]
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).

WarningWarning
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)
Tags :


Page view tracker