SQL Monitor 10

Permissions required for monitoring servers

For any server, you can:

  • Use the Windows / Active Directory Monitoring service account to access both Windows and SQL Server if you have selected a Windows service account for the Base Monitor service, or
  • Specify a Windows / Active Directory login on the monitored server to collect data from Windows, or
  • Specify either a Windows / Active Directory account or a SQL Server login to collect data from SQL Server.

In the first case, the Base Monitor service may be a group Managed Service Account (gMSA). 

for Azure SQL Databases and Managed Instances, see: Monitoring Azure SQL Servers.

The account used to monitor any server requires a number of permissions both within Windows and within any SQL Server instance.

If you want to access servers through a firewall, check the firewall requirements.

Monitoring the Windows host

The simplest configuration is achieved by making the monitoring account an administrator on the remote Windows host machine. If this isn't possible, see: Monitoring host Windows machines without admin permissions.

  • The account should have login rights locally on the machine where the Base Monitor is installed, so that SQL Monitor can authenticate the account.

Monitoring host Windows machines without admin permissions

  • If connecting via DCOM, the account should have Remote Launch and Remote Activation permissions on the target machine, and Remote Access for Anonymous Login. For information on how to assign these permissions, see: Securing a Remote WMI Connection.
  • If connecting via WinRM the account should be a member of the WinRMRemoteWMIUsers__ group (2 underscores). For further information, see: Authentication for Remote Connections.
  • The account should have Remote Enable and Enable Account permissions for the WMI namespaces CIMV2, Root and MSCluster (if it exists on the machine). This can be done from WMI Control in Computer Management.
  • The account should be a member of the Performance Monitor Users group on the target machine.
  • For the next section you will need to obtain the SID of the monitoring account. The SID can be obtained by opening a command prompt and running:

    wmic useraccount where name='<account_name>' get sid
  • The account should have Create Child (CC)List Children (LC)Read Property (RP) and Read Control (RC) permissions for scmanager on the target machine to allow SQL Monitor to access the list of running Windows processes.  This can be done by adding (A;;CCLCRPRC;;;<sid>) to scmanager’s security descriptor, where <sid> is the SID of the account. An example of how this is done is shown below.

    sc sdset scmanager "D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CCLCRPRC;;;<sid>)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)"
  • The account should have List Children (LC) permissions on all SQL Server services on the target machine to ensure that they are visible to SQL Monitor. This can be done by adding (A;;LC;;;<sid>) to the security descriptor of each service, replacing <sid> with the SID of the account as obtained above. An example of how this is done is shown below:

    sc sdset MSSQL$SQL2012CS "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;LC;;;<sid>)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"
  • If the server is part of a Windows Server Failover Cluster, e.g. it hosts part of a  SQL Server Always On Failover Cluster Instance or part of an Always On Availability Group, SQL Monitor will require access to the Windows Server Failover Cluster to identify and monitor cluster resources.  On the monitored machine, run the following PowerShell as an administrator, substituting in the account you are using for monitoring:

    > Import-Module FailoverClusters
    > Grant-ClusterAccess -User <accountname> -Full

Monitoring SQL Server instances

The account used to monitor your SQL Server instance should have the following permissions:

  • Member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to turn on the deadlock trace flag).
If you are unable to grant sysadmin permissions to the account, please see Monitoring servers without sysadmin permissions for how to grant the following permissions:
  • Member of the db_datareader role on the msdb system database.
  • Member of SQLAgentReader role on the msdb system database.
  • Member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
  • VIEW ANY DEFINITION server permission.
  • ALTER TRACE server permission (if you want to enable trace data).
  • VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  • Member of the db_owner  role on the tempdb database.
  • EXECUTE on xp_readerrorlog.

Monitoring Azure SQL Servers

  • The account used to monitor your Azure SQL Server must be the server admin account used to create the Azure SQL Server.

Monitoring Amazon RDS SQL Servers

  • The account used to monitor your Amazon RDS SQL Server must be the server admin account provided upon creation of the RDS instance.

Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?