Sql-server – Server Activity data collector job fails due to performance counters

tl;dr: what Windows permission / configuration is needed for the "Server Activity" data collection job to run successfully?

I'm running SQL Server 2016 SP1 CU6 (13.0.4457.0) on Windows Server 2012 R2 Datacenter.

Whenever I try to run the "Server Activity" data collector, the SQL Server Agent job fails, and I see the following three errors in the Application event log on the box.

Unable to open the Server service performance object. The first four bytes (DWORD) of the Data section contains the status code.

^{Source: PerfNet, Event ID: 2004}

The Open Procedure for service "WmiApRpl" in DLL "C:\Windows\system32\wbem\wmiaprpl.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

^{Source: Perflib, Event ID: 1008}

The Open Procedure for service "BITS" in DLL "C:\Windows\System32\bitsperf.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

^{Source: Perflib, Event ID: 1008}

This is happening on all 4 of the SQL Server boxes where I'm running these collectors. The other two collectors (Query Statistics and Disk Usage) are working fine on all 4 servers.

Things I have tried that had no effect:

• Add SQL Server Agent user (Windows account) to the local "Performance Monitor Users" group (source)
• Reload the performance library with the "lodctr" command (source)
• Turn on the "WMI Performance Adapter" service and set to automatic (source)
• Run the lodctr command at C:\Windows instead of C:\Windows\system32 (source)

Finally, I tried temporarily adding the Windows account that SQL Server Agent runs as to the local administrators group on one of the boxes and rebooting it. This allowed the job to run successfully (although the BITS error still appeared in the application event log). Reversing this and rebooting caused the job to start failing again.

I don't want that service account to be a local admin on this box, and I'd be shocked if it needs to be. So what are the minimum permissions needed for this account to be able to access the performance counter data needed for this data collection set?

I have scripted out the job details, in case that is helpful:

USE [msdb]
GO

/****** Object:  Job [collection_set_2_collection]    Script Date: 3/1/2018 2:17:35 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Data Collector]    Script Date: 3/1/2018 2:17:35 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'collection_set_2_collection', 
        @enabled=0, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'Data Collector', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [collection_set_2_collection_collect]    Script Date: 3/1/2018 2:17:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'collection_set_2_collection_collect', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=3, 
        @retry_interval=5, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'dcexec -c -s 2 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" -m 0 -e $(ESCAPE_NONE(STOPEVENT))', 
        @flags=80
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [collection_set_2_collection_autostop]    Script Date: 3/1/2018 2:17:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'collection_set_2_collection_autostop', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=2, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec dbo.sp_syscollector_stop_collection_set @collection_set_id=2, @stop_collection_job = 0', 
        @database_name=N'msdb', 
        @flags=16
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunAsSQLAgentServiceStartSchedule', 
        @enabled=1, 
        @freq_type=64, 
        @freq_interval=0, 
        @freq_subday_type=0, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20160430, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'ec88e0b9-88cf-454b-a4c1-0397ef849519'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO