Setup MDW Job Proxy - SQL-FineBuild/Common GitHub Wiki

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

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 MDW Job Proxy configuration relates to Process Id 5EDG.

FineBuild uses the following parameters to help setup the MDW Job Proxy:

Parameter	Default Value	Description
/ManagementServer:	(none)	Name of SQL Instance hosting the MDW Database
/MDWAccount:	SQL Agent Account	Name of MDW Job Proxy account
/MDWPassword:	SQL Agent Password	MDW Job Proxy Account password

SQL FineBuild will automatically perform the actions described in Manual Setup MDW Job Proxy.

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.

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 [/MDWAccount:] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]
EXEC SP_ADDROLEMEMBER @ROLENAME='dc_proxy', @MEMBERNAME='/MDWAccount:'
EXEC sp_addsrvrolemember '/MDWAccount:', 'sysadmin'
```
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 supplied for these parameters
```
CREATE CREDENTIAL [/MDWAccount:] WITH IDENTITY = N'/MDWAccount:', SECRET = N'/MDWPassword:'
EXEC sp_add_proxy @proxy_name=N'/MDWAccount:', @credential_name=N'/MDWAccount:', @enabled=1, @description=N'MDW Proxy'
EXEC sp_grant_proxy_to_subsystem @proxy_name=N'/MDWAccount:', @subsystem_id=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 
JOIN sysjobs j 
  ON j.job_id      = s.job_id 
JOIN syscategories c 
  ON j.category_id = c.category_id 
 AND c.name        = 'Data Collector' 
LEFT JOIN sysproxies p 
  ON p.name        = '/MDWAccount:' 
WHERE s.subsystem   = 'CMDEXEC' 
  AND s.proxy_id    IS NULL

Previous Set Data Collector Job Names	Top	Disable System Database Collection Next