Sql-server – Proxy for MDW data collection on remote servers – SQL Server 2012

management-data-warehousesql serversql-server-2012

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?

Here are my options:

Best Answer

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.

Related Solutions

Sql-server – How to configure data collection in SQL Server 2012

It's a bug. This Connect ticket has a workaround for it from Kendra Little:

I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I went back and set the Utility Information collection to manual start, thereby removing the schedule.
declare @schedule_uid uniqueidentifier
select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id=3

exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid

SQL Server – Linked Servers and Remote Statistics

What I do is this 'trick':

Give this 'linkuser' read, whatever else needed and ddl_admin privileges, then run the following script to ensure that none of the ddl_admin privileges can actually be used.

But by being technically member of ddl_admin the user can now see statistics in the remote db! (note: from SQL2012SP1 there is no need for this hack, the stats are visible as they should be)

DENY CREATE TABLE TO [LinkUser]
DENY CREATE VIEW TO [LinkUser]
DENY CREATE PROCEDURE TO [LinkUser]
DENY CREATE FUNCTION TO [LinkUser]
DENY CREATE RULE TO [LinkUser]
DENY CREATE DEFAULT TO [LinkUser]
DENY CREATE TYPE TO [LinkUser]
DENY CREATE ASSEMBLY TO [LinkUser]
DENY CREATE XML SCHEMA COLLECTION TO [LinkUser]
DENY CREATE SCHEMA TO [LinkUser]
DENY CREATE SYNONYM TO [LinkUser]
DENY CREATE AGGREGATE TO [LinkUser]
DENY CREATE MESSAGE TYPE TO [LinkUser]
DENY CREATE SERVICE TO [LinkUser]
DENY CREATE CONTRACT TO [LinkUser]
DENY CREATE REMOTE SERVICE BINDING TO [LinkUser]
DENY CREATE ROUTE TO [LinkUser]
DENY CREATE QUEUE TO [LinkUser]
DENY CREATE SYMMETRIC KEY TO [LinkUser]
DENY CREATE ASYMMETRIC KEY TO [LinkUser]
DENY CREATE FULLTEXT CATALOG TO [LinkUser]
DENY CREATE CERTIFICATE TO [LinkUser]
DENY CREATE DATABASE DDL EVENT NOTIFICATION TO [LinkUser]
DENY CHECKPOINT TO [LinkUser]
DENY ALTER ANY SCHEMA TO [LinkUser]
DENY ALTER ANY ASSEMBLY TO [LinkUser]
DENY ALTER ANY DATASPACE TO [LinkUser]
DENY ALTER ANY MESSAGE TYPE TO [LinkUser]
DENY ALTER ANY CONTRACT TO [LinkUser]
DENY ALTER ANY SERVICE TO [LinkUser]
DENY ALTER ANY REMOTE SERVICE BINDING TO [LinkUser]
DENY ALTER ANY ROUTE TO [LinkUser]
DENY ALTER ANY FULLTEXT CATALOG TO [LinkUser]
DENY ALTER ANY SYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY ASYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY CERTIFICATE TO [LinkUser]
DENY REFERENCES TO [LinkUser]
DENY ALTER ANY DATABASE DDL TRIGGER TO [LinkUser]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [LinkUser]

Best Answer

Related Solutions

Sql-server – How to configure data collection in SQL Server 2012

SQL Server – Linked Servers and Remote Statistics

Related Question