When collecting data from SQL Server instances hosted in Amazon Web Services (AWS), configuration requirements vary depending on whether the instance is an EC2-hosted SQL Server or an Amazon RDS instance.
To minimize latency, host the WISdom Data Collector VM in the same AWS region as the monitored instances. Hosting the collector outside AWS is supported but may affect collection reliability.
EC2 Instances
SQL Server on EC2 follows the same permission model as on-premises or self-hosted SQL Server. SA permissions provide full collection capability. For least-privilege configuration, refer to the SQL Instance Collection page.
For WMI collection on EC2 instances, configure the least-privilege group memberships and WMI namespace security as described on the Windows Collection Requirements page.
RDS Instances
Amazon RDS does not permit direct access to core SQL Server system tables. WISdom collects data through RDS-specific views that Amazon exposes on top of the underlying system objects. Custom server roles are not available on RDS — all server-level permissions must be granted directly to the collection account.
Use a SQL-authenticated account created in master, msdb, and all monitored user databases.
For the recommended approach of using a custom database role rather than granting permissions directly to a service account, see SQL Collection Requirements. The script below implements this pattern using db_wisdom_rds_collector.
Master Database
Server-level permissions must be granted directly to the collection account — custom server roles are not available on RDS.
USE [master];
GO
EXEC sp_addrolemember N'processadmin', N'[YourCollectionAccount]';
GO
GRANT VIEW SERVER STATE TO [YourCollectionAccount];
GRANT VIEW ANY DEFINITION TO [YourCollectionAccount];
GRANT CREATE DATABASE TO [YourCollectionAccount];
GRANT VIEW ANY DATABASE TO [YourCollectionAccount];
GRANT ALTER TRACE TO [YourCollectionAccount];
GO
MSDB Database
USE [msdb];
GO
-- Optional: Create a custom database role for WISdom monitoring
-- Skip this block if granting permissions directly to the collection account
CREATE ROLE [db_wisdom_rds_collector];
GO
-- Add the custom role to the built-in SQL Agent role
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [db_wisdom_rds_collector];
GO
-- Grant explicit execution rights on RDS-specific tasks
GRANT EXECUTE ON rds_backup_database TO [db_wisdom_rds_collector];
GRANT EXECUTE ON rds_restore_database TO [db_wisdom_rds_collector];
GRANT EXECUTE ON rds_task_status TO [db_wisdom_rds_collector];
GRANT EXECUTE ON rds_cancel_task TO [db_wisdom_rds_collector];
-- Grant explicit SELECT rights on required agent tables
GRANT SELECT ON sysjobs TO [db_wisdom_rds_collector];
GRANT SELECT ON sysjobhistory TO [db_wisdom_rds_collector];
GRANT SELECT ON sysjobactivity TO [db_wisdom_rds_collector];
-- sysoperators is not included in SQLAgentUserRole and must be granted explicitly
GRANT SELECT ON sysoperators TO [db_wisdom_rds_collector];
GRANT EXECUTE ON agent_datetime TO [db_wisdom_rds_collector];
GO
-- Optional: Add the collection account to the custom database role
ALTER ROLE [db_wisdom_rds_collector] ADD MEMBER [YourCollectionAccount];
GO
sysoperators is not included in SQLAgentUserRole. It must be granted explicitly.
User Databases
-- Run in each monitored user database
GRANT SHOWPLAN TO [YourCollectionAccount];
GO
If you are using the optional Query Store role (db_wisdom_qs_collector), include GRANT SHOWPLAN in that role's script rather than granting it directly to the collection account. This keeps all user database permissions consolidated under a single role. See the Query Store section below.
Query Store Permissions (Optional)
When Query Store is enabled on a user database, WISdom uses it for more efficient query statistics collection with reduced server impact compared to DMV-based collection. The collection account must be added to each database where Query Store is enabled.
GRANT SHOWPLAN is included in this script. If you are using the db_wisdom_qs_collector role, this replaces the standalone GRANT SHOWPLAN in the User Databases section above — do not grant it twice.
-- Run in each user database where Query Store is enabled
-- Optional: Create a custom database role for Query Store access
-- Skip this block if granting permissions directly to the collection account
CREATE ROLE [db_wisdom_qs_collector];
GO
GRANT SHOWPLAN TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.database_query_store_options TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_context_settings TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_plan TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query_text TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query_hints TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_wait_stats TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_runtime_stats TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_runtime_stats_interval TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.database_query_store_internal_state TO [db_wisdom_qs_collector];
GO
-- Optional: Add the collection account to the custom database role
ALTER ROLE [db_wisdom_qs_collector] ADD MEMBER [YourCollectionAccount];
GO