SQL Server Run As Profiles
Management Pack for SQL Server provides the following Run As profiles:
Microsoft SQL Server Discovery Run As Profile
This profile is associated with existing discoveries.
Microsoft SQL Server Monitoring Run As Profile
This profile is associated with existing monitors and rules.
Microsoft SQL Server Task Run As Profile
This profile is associated with existing tasks.
Microsoft SQL Server SCOM SDK Run As Profile
This profile is for SQL Server MP workflows that need access to System Center Operations Manager SDK.
Management Pack for SQL Server needs an author set of privileges on the System Center Operations Manager SDK to create a management pack and store overrides in it.
If the default action account on System Center Operations Manager doesn't have these permissions, create such an account and map it to the Microsoft SQL Server SDK Run As Profile.
Microsoft SQL Server SQL Credentials Run As Profile
This profile is used for agentless monitoring mode only.
Note
Don't bind accounts to the Microsoft SQL Server SQL Credentials Run As Profile if you monitor SQL Server in agent or mixed monitoring modes, as only a basic action account can be bound to this profile. Also, don't use a Windows account or non-basic account with this profile.
When using agent or mixed monitoring mode, all discoveries, monitors, and tasks use accounts from the Default Action Account Run As profile. If the default action account for a given system doesn't have the necessary permissions to discover and monitor instances of SQL Server, such systems can be bound to more specific credentials defined in Microsoft SQL Server Run As Profiles, group Managed Service Accounts (gMSAs) are also supported.
For more information about Run As accounts, see Managing Run As accounts and profiles.
Enabling Allow Log On Locally Security Policy
If the domain account is used as an action account, enable the Allow log on locally policy on both SQL Server on Windows and SQL Server on Linux.
Agent and Mixed Monitoring Modes
To configure Run As profiles, use one of the following scenarios:
Action Account is Local Administrator and SA
Action Account is Local Administrator w/o SA
Action Account is Local System w/o SA
Action Account is Local Administrator and SA
A System Center Operations Manager default action account is mapped to either local system account or any domain user account that is a member of the local administrators' group on the monitored machines.
The account that you use must be granted the SQL System Administrator (SA) rights on the monitored SQL Server instances.
A domain user account can be granted SA rights by granting them to the BUILTIN\Administrators local group in the SQL Server security access list. In this case, monitoring of SQL Server instances will work out of the box, except for some configurations described below.
Follow these steps to ensure that all requirements are met:
If you store SQL Server databases on an SMB file share, the default action account must have the rights, as described in Low-Privilege Agent Monitoring.
In cases when servers hosting Always On Availability Replicas (at least one of them) have a machine name consisting of more than 15 characters, ensure to perform the steps described in Monitoring of AG on Windows Servers with Long Names.
Action Account is Local Administrator w/o SA
A System Center Operations Manager default action account is mapped to either local system account or domain user account, but SA rights can't be granted to this account due to security policies.
If the security policy allows granting SA rights to the separate domain user account used for launching SQL Server MP workflows only, perform the following steps:
Create a new domain user account and add this account to the Local Administrators' group on each monitored server.
Grant SA rights to this account on SQL Server.
Create a new action account in System Center Operations Manager and map this account to the domain user account created above.
Map the new action account to all SQL Server MP Run As Profiles.
If you store SQL Server databases on an SMB file share, the default action account must have the rights, as described in Low-Privilege Agent Monitoring.
Action Account is Local System w/o SA
[This scenario is for agent monitoring mode only.]
A System Center Operations Manager default action account is mapped to either local system account or domain user account, but SA rights can't be granted to this account due to security policies that forbid access to SQL Server.
You can grant SA or low privilege rights to System Center Operations Manager HealthService using its Service Security Identifier. For more information, see Security identifiers.
Follow these steps to configure the security configuration using Service SID.
Agentless Monitoring Mode
To configure Run As Profiles in agentless monitoring mode, create an account on SQL Server and grant this account SA rights or a set of low privilege permissions. You can use SQL Server authentication or Windows authentication. Once created, you can use this account in the Add Monitoring Wizard to add SQL Server instances.
For more information on how to configure low privilege monitoring in agentless monitoring mode, see the Low-Privilege Monitoring.
Monitoring of AG on Windows Servers with Long Names
Regardless of whether you use a local system account, domain user account, or rights assignment, permissions listed below are required.
Example: You've three replicas in your Availability Group that are hosted on the following computers:
- Computer_1
- Computer_2
- Computer_3
Computer_1 hosts the primary replica. In this case, you should configure security settings for Computer_1 on Computer_2 and Computer_3. If Computer_2 is going to host the primary replica after failover, other computers should also have WMI security configured for this computer.
The local system account of each node that might act as the primary one must have WMI permissions for other nodes in the current Availability Group. The same is for the domain action account.
Below are the steps to configure security for configurations with the local system account. The provided instruction suggests that the SQLAON-020 computer hosts the primary replica. These steps should be taken on each replica that participates in the target Availability Group.
To configure permissions for Always On workflows when server names exceed 15 characters, perform the following steps:
Launch mmc.exe and add the following snap-ins:
- Component Services
- WMI Control (for local computer)
Expand Component Services, right-click My Computer, and select Properties.
Open the COM Security tab.
In the Launch and Activation Permissions section, select Edit Limits.
In the Launch and Activation Permission window, enable the following permissions for the remote machine account:
- Remote Launch
- Remote Activation
Open WMI Control snap-in properties, go to the Security tab, select the Root\CIMV2 namespace, and select Security.
Allow the following permissions for the target computer:
- Enable Account
- Remote Enable
Select Advanced, select a target account, and select Edit.
From the Applies to dropdown list, select This namespace only.
In the Permissions section, enable the following checkboxes:
- Enable Account
- Remote Enable
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for