I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I give it rights to?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Operating system (CmdExec). The reason for this is that the documentation at https://msdn.microsoft.com/en-us/library/bb630341.aspx states:
Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.
So you need to modify anything which is running the SSIS jobs. In this case if you inspect the jobs themselves you'll see they're run using CmdExec running dcexec which runs the packages. So those are the ones you'd need a proxy on if you're interested in if you have that security issue.