4

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

1 Answer 1

1

Add the account to the Performance Monitor Users local group on the server you want to monitor; that group encapsulates all the required security rights required for looking at performance monitor data.

Perhaps the Data Collector error logs will show some detailed error messages?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.