dcexec login failure

Question

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?

Answer 1

The problem has been solved with some hints from:

• Performance Data Collector in 2008
• Data Collector - Cannot Find Database

When I setup Data Collection I did the following:

Setup

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

Configuration

Configure Management Data Warehouse
Next
 > Check: Set up data collection
Next
 > Server name: serverA.company.com
 > Database name: DBA_MDW
 > Cache directory:
Next
Finish
Close

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 serverA.company.com. That seems to have made all the difference and I'm now collecting data.