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'm trying to set up a linked server between 2 instances of Sql Server 2008 R2.

The login works perfectly when I select the impersonate option. However when I try to use the remote user/password option it fails, even though the remote user is the same as the local user being impersonated.

The user account I'm trying to connect with is a windows admin on both machines.

Unfortunately, I need to be able to connect to machines that the local user doesn't have admin rights to (though I will have the username/password of an admin when this occurs), so impersonation won't work.

Where am I going wrong?

share|improve this question
    
And the remote login to which you are mapping is set up on your remote server, correct? –  jl01 Mar 19 '12 at 20:46
    
@jl01- The remote login has admin rights to the remote server, and I am able to connect directly to the machine, as well as to the SQL Server instance using Windows Authentication. –  dckrooney Mar 19 '12 at 20:53
    
Clearly your instance is accepting remote connections. If the remote login is set up with in SQL Server on the remote server you should be able to get there. I have found that when I have set up a login to map remotely I have mis-typed the password. What is the error that you are getting? –  jl01 Mar 19 '12 at 21:14
    
The error I'm receiving is "Login failed for user: ____". And I'm very sure I haven't mistyped a password, since I've entered it MANY times. :) –  dckrooney Mar 19 '12 at 21:58
    
Double-checking, I see that when setting mapping a login in a linked server and deliberately using an incorrect password, I get an immediate error (Login failed) when clicking OK. I'm stumped. I could see a login password being changed on a remote server AFTER the linked server mapping is set up, but you've logged into SQL Server remotely using your mapping login. Arrrgh. –  jl01 Mar 19 '12 at 22:23

2 Answers 2

user/password option it fails, even though the remote user is the same as the local user being impersonated

You can't possibly impersonate a SQL login, and a linked server user/password option only allows for SQL logins. Sounds like you are attempting to pass in a Windows user/password credential, something that is not supported.

share|improve this answer

Sounds like an impersonation issue. Have you set trustworthy? Specifically:

"With the introduction of EXECUTE AS, crossing link servers as an impersonated user or login becomes a possibility. To make this happen, the principle (user\login) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy. To make the databases trustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuing an ALTER DATABASE command such as this:

ALTER DATABASE mydatabase SET TRUSTWORTHY ON"

share|improve this answer

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.