3

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?

Improve this question
15
  • 1
    Are the two computers in the same domain?
    – Jon Seigel
    Commented Jul 24, 2012 at 13:46
  • I've got no idea where VPN puts the connecting computer but I suppose they aren't. Otherwise it would not be an untrusted domain, would it? Why is that even important? I'm not trying to use Windows authentication. I guess I'm missing some major details about how this thing works...
    – gemisigo
    Commented Jul 24, 2012 at 21:29
  • I think it may have something to do with the Windows principal that's implicitly used when you open Management Studio.
    – Jon Seigel
    Commented Jul 24, 2012 at 21:36
  • Thanks for the info, Jon, that's interesting. I wonder why does it not complain about when I'm simply connecting to the server and run a query. Any idea how to solve that? I'm not the sharpest knife in the drawer when it comes to domains :) Is it absolutely necessary for the computers to be in the same domain? What happens when there's VPN involved?
    – gemisigo
    Commented Jul 24, 2012 at 21:54
  • VPN is a networking construct to join multiple separate networks as if they were one, so that shouldn't affect anything. It's still unclear to me what the domain topology is like on your network... Is the SQL Server box in a different domain than your workstation?
    – Jon Seigel
    Commented Jul 24, 2012 at 22:27

1 Answer 1

Reset to default
1

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.

Improve this answer
7
  • Yes, the issue and its reasons have been ascertained yet I still have troubles with the workarounds. I don't understand the first one and cannot get the second one to work.
    – gemisigo
    Commented Jul 26, 2012 at 9:02
  • @gemisigo: See edit. I realized that workaround #1 would require that you know the password to that account.
    – Jon Seigel
    Commented Jul 26, 2012 at 13:18
  • You're helping me a lot, Jon, thanks. I was able to accomplish #3 and while I'm pretty happy it works, I'm aiming at #2. A domaint acc was created and added to SQL Server with the required permissions but the problems start earlier. I cannot run anything on the workstation (with local login) trying different user using mydomain\mydomainuser for account. Not even Calculator is willing to start. I suppose I don't fully understand what 'impersonate it remotely' mean.
    – gemisigo
    Commented Jul 27, 2012 at 9:04
  • 1
    @gemisigo: Sorry, my bad. Cross-domain impersonation requires domain trusts to set up, which you don't have and can't do. You're now at the optimal solution with new matching local logins (the new domain account can be removed).
    – Jon Seigel
    Commented Jul 27, 2012 at 13:03
  • Oh, I see. Or at least I think so. So, if I have two logins, both alike in dignity, I mean matching username AND password, one on the SQL box and the other one on the workstation (expediently, duplicating the one on the workstation to the SQL box), and giving it the required SQL permissions then I'll be able to run the reports, regardless of how restricted that login is in Windows on the SQL box, right?
    – gemisigo
    Commented Jul 27, 2012 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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