SentryOne v19.1.1 : Monitoring Service Security Requirements
Since the beginning of SQL Sentry, the monitoring service account has required sysadmin privileges on SQL Server to add the instance as a watched target or collect performance data from it. Requiring the sysadmin role has created challenges and concerns for some, particularly those working in the healthcare and finance industries. Historically, much of this requirement had to do with SQL Server itself. Over time, Microsoft added new methods, including DMVs, which allowed access to information without requiring the sysadmin role. At SentryOne, we recognized these changes from Microsoft as an opportunity to update how SQL Sentry accesses certain information to take advantage of less omnipotent security roles.
Starting with Version 19.1.1, SQL Sentry no longer requires the monitoring service account to have the sysadmin role to monitor a SQL Server. If the monitoring service account and interactive user do not have the sysadmin role, then:
- They must still be a principal on the SQL Server target with the CONTROL SERVER permission granted.
- In addition, they must be a member of the SQLAgentOperatorRole role on the msdb database. Note that this automatically adds membership to the SQLAgentReaderRole and SQLAgentUserRole.
- Furthermore, depending on the exact SP/CU of the SQL Server version, the securityadmin server role may be required to guarantee the collection of SQL Agent Log events.
|Server Roles||User Mapping||Securables|
There are some SQL Server version requirements and limitations. Prior to SQL Server 2012, substantial data for performance counters, disk activity, and disk space are missing without the sysadmin role. We do not officially support watching targets that are running SQL Server 2008 R2 or earlier without using the sysadmin role. In addition, 2008 R2 and earlier versions of SQL Server have not been fully tested, so there may be other limitations that are unknown. If you're not aware, you should know that these older versions of SQL Server have or will soon reach their end of support with Microsoft (July 9, 2019 for 2008 and 2008 R2).
There are some limitations without the sysadmin server role:
Use SQL Server 2012+
- Securables, and thus, the CONTROL SERVER permission, were introduced in SQL Server 2005; however, we strongly advise against watching these older versions without the sysadmin role, and as mentioned above, 2005, 2008, and 2008 R2 are not supported.
Disk Activity / Disk Space
- Most SQL file and stats information cannot be collected for SQL Server 2008 R2 and earlier targets.
- VLF and log file data will not be collected for SQL Server targets that are on 2016 SP1 or earlier.
Last DBCC CHECKDB time
- This information may only be available in the following versions with the appropriate SP or CU:
- 2014 SP 3+
- 2016 SP 2+
- 2017 CU 7+
SQL Server Agent
- Start or stop the SQL Server Agent from the SentryOne client
- Feature is not available if the target is watched in limited access mode.
- Feature is available if watching the target in full mode and the interactive user is a Windows admin on the associated watched Windows target for that SQL Server.
- The watch status of SQL Agent Alerts cannot be changed without the sysadmin server role.
- The collection and monitoring of SQL Agent Logs may not be available for all SQL Server versions prior to 2012, or 2012+ versions without the required service pack updates.
To download a free trial of SQL Sentry Version 19.1.1, visit the SQL Sentry trial page.
Melissa (@MelikaNoKaOi) is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.