This project has moved. For the latest updates, please go here.

Previous Set Data Collector Job Names Manual Configuration Disable System Database Collection Next

Setup MDW Job Proxy

FineBuild can setup the MDW Job Proxy.

FineBuild can configure the Management Data Warehouse function to use a central database to hold details for all servers. When using a central MDW, it may be necessary to create a MDW Job Proxy.

To get FineBuild to use a central MDW, you must supply a /ManagementServer: parameter for all SQL Server builds. The value of the /ManagementServer: parameter should identify the location of the central MDW database.

By default, FineBuild will use the SQL Agent service account to pass data to the central MDW server. In this situation no MDW Job Proxy is required.

If you want to use a different SQL Agent account for each server, then you must use a proxy account to pass the data. The proxy account is specified using the /MDWAccount: and /MDWPassword: parameters.

FineBuild Setup MDW Job Proxy

The configuration of the MDW database within FineBuild is broken down into a number of stages, in order to simplify restart processing if any of them fails.

This section describes how to setup the MDW Job Proxy. The MDW Job Proxy configuration relates to Process Id 5EDG.
Top

Manual Setup MDW Job Proxy

The following steps show what you would have to do for manual setup of the MDW Job Proxy. FineBuild does all of this work for you automatically.

Replace the string MDWAccount_ with the account name you are using for the Proxy.

1) Setup Job Proxy Security

The Proxy account must be added to the database roles needed for it to work.
This version of FineBuild assigns the proxy account to the Sysadmin role. At the time of writing, Microsoft has not documented the authorities needed to use a low-priviledge account for the MDW Job Proxy, so the Sysadmin authority must be used. Progress is being made by the SQL Server Community to work out what low-priviledge authorities are needed, and it is hoped that a future version of FineBuild can use these.
CREATE LOGIN [MDW_Account] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_proxy', @MEMBERNAME='MDW_Account'
EXEC sp_addsrvrolemember 'MDW_Account ', 'sysadmin'

2) Create the Job Proxy Credential

This step should not be done if the SQL Agent service account is being used to connect to the central MDW database.
A Credential must be created for the MDW Job Proxy Account. Run the following commands, replacing MDWAccount and MDWPassword with the values you have specified using the /MDWAccount: and /MDWPassword: parameters.
CREATE CREDENTIAL [MDW_Account] WITH IDENTITY = N'MDW_Account', SECRET = N'MDW_Password'
EXEC sp_add_proxy @proxy_name=N'MDW_Account', @credential_name=N'MDW_Account', @enabled=1, @description=N'MDW Proxy'
EXEC sp_grant_proxy_to_subsystem @proxy_name=N'MDW_Account', @subsystem_id=3

3) Apply the Job Proxy to MDW Jobs

This step should not be done if the SQL Agent service account is being used to connect to the central MDW database.
Run the following command to apply the MDW Job Proxy to the MDW job steps:
UPDATE sysjobsteps SET 
 proxy_id = p.proxy_id 
FROM sysjobsteps s 
INNER JOIN sysjobs j 
   ON j.job_id      = s.job_id 
INNER JOIN syscategories c 
   ON j.category_id = c.category_id 
  AND c.name        = 'Data Collector' 
 LEFT JOIN sysproxies p 
   ON p.name        = 'MDW_Account' 
WHERE s.subsystem   = 'CMDEXEC' 
  AND s.proxy_id    IS NULL

Copyright © 2014 Edward Vassie. License and Acknowledgements
Previous Set Data Collector Job Names Top Disable System Database Collection Next

Last edited Jul 26, 2014 at 11:54 AM by EdVassie, version 2