4

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?

Improve this question
5
  • And the remote login to which you are mapping is set up on your remote server, correct?
    – jl01
    Mar 19, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 22:23

3 Answers 3

Reset to default
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.

Improve this answer
0

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"

Improve this answer
0

Are you sure about the "Trustworthy" setting? I know that is required for many scenarios using EXECUTE As between databases on the same server but I did not know it had an affect on Linked Servers. Perhaps you are referring to the A/D setting "Trust For Delegation" which must be set for on the server computer(s)

Improve this answer

Your Answer

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

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