March 8, 2011 at 3:54 pm
I am trying to setup data collection and upload to an MDW on a remote server. During the setup wizard i can connect with the sa account but the upload job seems to insist on using windows authentication and the account doesn't have permission on the remote server.
This article states that i need to "make sure that the SQL Server Agent account will have access to the MDW database if it has been configured on a remote server. To ensure this access, you can create a SQL Server Agent proxy account and add it to the dc_proxy database role for the msdb database."
However do i need to do this on the server where the data is collected or on the server to which the data is uploaded? Also it seems i have to use a credential to setup the proxy. What credential do i need to use?
Thanks very much for all advice and tips.
March 8, 2011 at 5:14 pm
Hi Jules,
I went through this last week while configuring MDW in a test environment.
Assuming two servers, MDW and REMOTE on the same AD domain, the simplest solution is to take the AD login used to run SQL Agent on REMOTE, and create a Windows login in SQL Server on MDW. Then create a user inside your MDW database (obviously on the MDW server), and grant this user the mdw_writer role inside the MDW database.
I haven't tested the proxy method, but this should be correct. The idea is to use a different account to connect to the MDW server. You might create a new account in Active Directory (say DOMAIN\MDW_WRITER), and create a login on the MDW server for this account, grant access to the MDW database, and grant the mdw_writer role. Just as we did in the previous example.
You then create a credential on REMOTE for the DOMAIN\MDW_WRITER account. Next, you create a proxy on REMOTE to use this credential, and add the MSDB role "dc_proxy" as one of the Proxy account principals.
Now, when your collectors try to upload to the MDW database, SQL Agent will attempt to log onto the MDW server as DOMAIN\MDW_WRITER.
Phew.
If you have 50 instances, and each SQL Agent instance runs under a different AD account (or a local account not on the domain), then creating the proxy will avoid you having to create 50 logins on the MDW server.
March 9, 2011 at 12:41 am
March 9, 2011 at 1:48 am
Hi Jules,
If you create a local Windows account on both machines (MDW and REMOTE) with the same username and password (e.g. MDW\mdw_user and REMOTE\mdw_user), they should be able to connect - this used to be the case, but I haven't tried it in a couple of years. Once this is done, you can either use this account to run SQL Agent, or use this account as your proxy account.
I haven't tried using a SQL login to connect the collectors to the MDW - I'm not sure if that's possible. It would be interesting though, as I do have a couple of servers not on my domain (although I don't think I'd want servers in a DMZ to connect back to the MDW - sort of defeats the point of the DMZ!)
May 11, 2011 at 4:30 pm
Assume my two servers , MDW and REMOTE on different AD domains.
MDW is SQL Server 2008 R2 Enterprise Edition on X64 platform. REMOTE is SQL Server 2008 (version 10.0.4000.0) on X64 platform.
It appears it is not possible to configure data collection on server 'REMOTE' to collect and upload to Management Data Warehouse on server 'MDW', as I see this error in the collector logs – “Failed to connecto to the management data warehouse server Inner Error” including that typo ‘connecto’ even though the Configure Management Data warehouse wizard on REMOTE allows you to use sql authentication to specify MDW database.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply