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
In Active Directory, create three domain users that will be commonly used for low-privilege access to all target SQL Server instances:
SQLDefaultAction
SQLDiscovery
SQLMonitor
Create a domain group named SQLMPLowPriv and add the following domain users:
SQLDiscovery
SQLMonitor
Grant special permission: Read-only Domain Controllers - “Read Permission” to the SQLMPLowPriv.
To configure the low-privilege environment on the agent machine
On the agent machine, add the SQLDefaultAction and SQLMonitor domain users to the “Performance Monitor Users” local group.
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.
- If the operating system of the agent machine is Windows Server 2003 or Windows Server 2003 R2, ensure that SQLDefaultAction and SQLMonitor have read access to the Application Event Log and System Event Log. For more information, see How to set event log security locally or by using Group Policy in Windows Server 2003.
Add the SQLDefaultAction domain user and SQLMPLowPriv domain group as members to the local Users group.
Configure the “Log On Locally” local security policy setting to allow the SQLDefaultAction domain user and SQLMPLowPriv domain group users to log on locally.
Grant Read permission on HKLM:\Software\Microsoft\Microsoft SQL Server registry path for SQLDefaultAction and SQLMPLowPriv.
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.
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
Grant “Remote Launch” and “Remote Activation” DCOM permissions to the SQLMPLowPriv, SQLDefaultAction using DCOMCNFG.
Allow Windows Remote Management through the Windows Firewall.
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
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:
VIEW ANY DEFINITION
VIEW SERVER STATE
VIEW ANY DATABASE
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.
Add the SQLMPLowPriv user on msdb to the “SQLAgentReaderRole” database role.
Add the SQLMPLowPriv user on msdb to the “PolicyAdministratorRole” database role.
To configure the instances for default action in SQL Server Management Studio
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:
VIEW ANY DEFINITION
VIEW SERVER STATE
VIEW ANY DATABASE
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.
Add a SQLDefaultAction user on msdb to the “SQLAgentReaderRole” database role.
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
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 theSC 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)
In SQL Server Management Studio, add “SQLDefaultAction” to db_owner database role for each database to check:
“Check Catalog (DBCC)”
“Check Database (DBCC)”
“Check Disk (DBCC)” (invokes DBCC CHECKALLOC)
Grant the ALTER privilege to SQLDefaultAction for each database on which to set state:
“Set Database Offline”
“Set Database Emergency State”
Grant the ALTER ANY DATABASE privilege to SQLDefaultAction login to run the task of “Set Database Online”.
To configure System Center Operations Manager
Import the SQL Server Management Pack if it has not been imported.
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
On the System Center Operations Manager console, configure the Run As profiles for the SQL Server Management Pack as following:
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.
Grant CONNECT to SQLDefaultAction for the OperationsManager database.
Add “SQLDefaultAction” to the dbmodule_users database role.
Set the “SQL Server Discovery Account” Run As profile to use the SQLDiscovery Run As account.
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)