Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
1  
Are the two computers in the same domain? –  Jon Seigel Jul 24 '12 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 Jul 24 '12 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 Jul 24 '12 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 Jul 24 '12 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 Jul 24 '12 at 22:27

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Jul 26 '12 at 9:02
    
@gemisigo: See edit. I realized that workaround #1 would require that you know the password to that account. –  Jon Seigel Jul 26 '12 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 Jul 27 '12 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 Jul 27 '12 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 Jul 27 '12 at 13:58

Your Answer

 
discard

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.