The SQL Sentry monitoring service is a Windows service that runs in the context of a domain account. It is used by SQL Sentry software (i.e. SQL Sentry) and SQL Sentry Portal.
- If the monitoring service account and interactive user do not have sysadmin privileges, then:
- They must, at a minimum, be a principal on the SQL Server target with the Control server permission granted.
- They must be a member of the SQLAgentOperatorRole role on the msdb database.
- This automatically adds membership to the SQLAgentReaderRole and SQLAgentUserRole.
- The securityadmin server role may be required to guarantee the collection of SQL Agent Log events, depending on the exact SP/CU of the SQL Server version.
- See the Update to change permissions for running sp_readerrorlog and sp_enumerrorlogs in SQL Server article from Microsoft for additional information.
- The account must also have Windows Administrator privileges on any computer with a watched Windows Task Scheduler instance to collect system level performance metrics with SQL Sentry Performance Analysis. If the monitoring service does not have Windows Administrator privileges, instance level metrics can still be collected using the Limited Access option.
- It isn't necessary for this account to be a domain administrator account.
- It's recommended that the service account be a standard user domain account that's added to the local administrators group of each monitored target.
- For more information about security and SQL Sentry Performance Analysis, see the Performance Analysis Security Requirements topic.
- GMSA (Group Managed Service Accounts) is supported through the Service Configuration Utility (see the Monitoring Service Logon Account article for instructions) and EPI Commands.
There are some limitations when not using sysadmin privileges:
- SQL Sentry will not be able to collect VLF and log file data for targets that are on SQL Server version 2016 SP 1 or earlier.
- Last DBCC CHECKDB time is available only if the targets have the following SQL Server versions with the appropriate SP or CU:
- 2014 SP 3+
- 2016 SP 2+
- 2017 CU 7+
- You will not be able to start or stop the SQL Server Agent from the SQL Sentry client unless the target Access Level is Full Access and the interactive user is a Windows admin on the associated Windows target for that watched SQL Server.
- The watch status of SQL Agent Alerts cannot be changed.
See the Microsoft Azure SQL Database and Data Warehouse Security article for account and firewall information required to monitor these target types.
After the initial installation, the Service Configuration Utility is used to update or change the credentials of the SQL Sentry monitoring service account. See the Monitoring Service Logon Account article for instructions.
If the Monitor Performance setting is set to False for a target, and you don't need to utilize General Performance Monitoring features, you may configure the monitoring service to use SQL Server Authentication. This is done through an instance's Monitoring Service Connection Properties.
To access the Monitoring Service Connection Properties for an instance complete the following steps:
- Open the Navigator pane (View > Navigator).
- Right-click the desired instance, and then select the Monitoring Service Connection Properties command to open the Service Connection Properties dialog.
- Uncheck Use Integrated Authentication, and then enter the SQL Server Authentication account you'd like the monitoring service to use for the instance. Select OK to save your changes.
You may wish to monitor an instance where OS level metrics through WMI and/or the Windows Performance Library are inaccessible. This is occasionally the case for cloud based or hosted servers. In these circumstances, a target may be added with Limited Access. This suspends attempts to access resources that are required for some functionality like the Disk Space and Activity tabs, and Windows Metrics on the Performance Analysis Dashboard. If access to those resources have been resolved, the Access Level can be set to Full Access in the Monitoring Service Connection Properties at the target level in the Navigator pane. Similarly, if a Watched target starts generating errors due to connectivity issues with the OS level resources that can't be resolved, changing the Access Level to Limited allows you to continue monitoring non-OS metrics without triggering connectivity errors for the target.
If the service fails to start, complete the following steps to start the service manually.
- For SQL Sentry software, it activates upon detecting a valid license on the SQL Sentry database.
- Depending on the software version, this may be the SQL Sentry Monitoring Service or SentryOne Monitoring Service.
- Open the Services window in Windows by selecting Control Panel > System and Security >Administrative Tools > Services.
- Select SentryOne Monitoring Service from the list of services. Right-click SentryOne Monitoring Service, and then select Start from the context menu or select the Play button on the toolbar to start the service.
Success: You've manually started the SQL Sentry Monitoring Service.