Sql-server – Data Collection reports fail because login is from an untrusted domain

data collectionreportingsql serversql-server-2008-r2ssms

I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management Studio on a different machine (which is using VPN to connect to the network the SQL Server is on). The error message is:

Failed to connect to server x.x.x.x. –> Login failed. The login is
from an untrusted domain and cannot be used with Windows
authentication.

The server uses mixed mode authentication. Data Collection is configured to use an SQL Login and the login that tries to generate the report is an SQL Login as well. They've got nothing to do with domains. Why do I get this error?

Best Answer

I have confirmed this is an issue with how the reports get executed on the server -- I reproduced the issue exactly by using two virtual machines, neither of which was in a domain.

Moreover, I created a test user (member of the Users built-in group and nothing more) on the SQL box, ran Management Studio locally as that user, logged in with sa, and attempted to run the report. As I expected, it failed, and returned the error Failed to connect to server MDWTestSQL. --> Login failed for user 'MDWTESTSQL\TestUser'.

This is clear indication that the reports are implemented to only use Windows authentication for some portion of it. Therefore, sufficient SQL-level permissions must be granted to the Windows principal running Management Studio, even if you authenticate to the server using a SQL login.

There are two workarounds:

  1. Use pass-through authentication: create a matching (user name and password) local login on the workstation, and impersonate the local login to run Management Studio remotely. (I tested that this works.)

  2. Use pass-through authentication by creating new matching local accounts on both boxes, and set up SQL permissions for the new account.

As I mentioned in chat, this problem is "fixed" in the 2012 Management Studio -- the reports in the menu are hidden if the Windows account doesn't have permissions to run the reports.

Related Question