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?