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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec.

Isolating that out, I can reproduce the issue by running:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1

I run the above command from a PowerShell window that I've opened with RunAs Administrator. The account I am running the above with is a domain account that is a local administrator on the server. In addition, the account also has the sysadmin role on the SQL instance.

The error that I get is:

SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540, 
Subcomponent: Generate T-SQL Package Task, 
Description: An error occurred with the following error message: 
"An error occurred while verifying the result set schema against the output table schema. 
The data collector cannot connect to the management data warehouse. : Login failed.
The login is from an untrusted domain and cannot be used with Windows authentication."..

The master package exited with error, previous error messages should explain the cause.

That is the same error I see in the history on the SQL Server Agent job.

Note that if I execute dcexec with a bad instance name, such as:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1

Then, as expected, I get a different error:

Failed to get the SQL server instance id from the given instance name.

Any ideas on how to successfully login to the SQL instance with dcexec?

share|improve this question
up vote 0 down vote accepted

The problem has been solved with some hints from:

When I setup Data Collection I did the following:


SSMS > serverA
Management > Data Collection
Configure Management Data Warehouse
 > Check: Create or upgrade a management data warehouse
 > Database name: DBA_MDW
 > Server name:
 > Database name: DBA_MDW
New Login
 > Login name: DOMAIN\SQLmdw
 > Check: mdw_admin
 > Check: mdw_reader
 > Check: mdw_writer


Configure Management Data Warehouse
 > Check: Set up data collection
 > Server name:
 > Database name: DBA_MDW
 > Cache directory:

To uninstall Data Collection, I used the script from Remove associated data collector jobs. I then followed the above setup and configuration steps, but this time under the configuration step I simply put serverA instead of That seems to have made all the difference and I'm now collecting data.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.