SQL Monitor 12

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 using a SQL Server instance name MSSQL$SQL2012CS is shown below (you will need to change this accordingly - the default service name is MSSQLSERVER):
```
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 information on disabling some samplers and also grant the following permissions:

Member of the db_datareader role on the msdb system database.

-- if the user account does not exist in all databases, the below script can be used to create it in every one of them
-- EXEC master.dbo.sp_MSforeachdb 'USE [?]; CREATE USER sql_monitor_account;'

USE msdb;
ALTER ROLE db_datareader ADD MEMBER sql_monitor_account;

Member of SQLAgentReader role on the msdb system database.

USE msdb;
ALTER ROLE SQLAgentReaderRole ADD MEMBER sql_monitor_account;

VIEW ANY DEFINITION server permission.

GRANT VIEW ANY DEFINITION TO sql_monitor_account;

ALTER TRACE server permission (if you want to enable trace data).
```
GRANT ALTER TRACE TO sql_monitor_account;
```

ALTER ANY EVENT SESSION server permission.

GRANT ALTER ANY EVENT SESSION TO sql_monitor_account;

VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.

GRANT VIEW SERVER STATE TO sql_monitor_account;
EXEC master.dbo.sp_MSforeachdb 'USE [?]; GRANT VIEW DATABASE STATE TO sql_monitor_account;'

There are two options for tempdb permissions, either granting membership of the db_owner role on the tempdb database:
```
USE tempdb;
ALTER ROLE db_owner ADD MEMBER sql_monitor_account;
```
Otherwise the need for this permission can be avoided by enabling the option to remove the need for this permission by running the following SQL on the SQL Monitor repository:
```
USE [SqlMonitor]
GO

INSERT INTO [settings].[KeyValuePairs]
           ([KeyName]
           ,[KeyValue]
           ,[KeyValueType])
     VALUES ('ReducePermissionsRequiredOnTempDb','True','BOOLEAN')
GO
```

EXECUTE on xp_readerrorlog.

GRANT EXECUTE on xp_readerrorlog TO sql_monitor_account;

SELECT on sys.sql_expression_dependencies on all databases.

EXEC master.dbo.sp_MSforeachdb 'USE [?]; GRANT SELECT ON sys.sql_expression_dependencies TO sql_monitor_account;'

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 either the server admin account provided upon creation of the RDS instance or a user that has following permissions below.

VIEW SERVER STATE permission

-- if the user account does not exist in all databases, the below script can be used to create it in every one of them
-- EXEC master.dbo.sp_MSforeachdb 'USE [?]; IF DB_NAME() NOT IN(''master'',''rdsadmin'') CREATE USER sql_monitor_account;'
USE master;
GRANT VIEW SERVER STATE TO sql_monitor_account

Member of SQLAgentOperatorRole role on the msdb system database.

USE msdb; 
EXEC sp_addrolemember SQLAgentOperatorRole, sql_monitor_account;

SELECT permission on sysjobs and sysjobhistory tables on the msdb system database.

USE msdb; 
GRANT SELECT ON sysjobhistory TO sql_monitor_account;
GRANT SELECT ON sysjobs TO sql_monitor_account;

SELECT permission on sys.sql_expression_dependencies on all databases.

EXEC master.dbo.sp_MSforeachdb 'USE [?]; IF DB_NAME() NOT IN(''master'',''msdb'',''rdsadmin'') GRANT SELECT ON sys.sql_expression_dependencies TO sql_monitor_account;'