How to set up linked server

[hencyt]

I have 2 SQL 2000 servers that I want to link. I used sp_addlinkedserver and also tried to set it up through enterprise manager. It seems to allow me to link the servers (the linked server appears in the sysservers table) but when I try to query any tables I get errors.
Currently I am getting this one:

error 18452: login failed for user(null). Reason: Not associated with a trusted SQL server connection.

I looked that up on microsoft but did not find anything that seemed to apply.

I have used SQL before but have not used linked servers. It may be a permissions problem.

Both servers are SQL 2000, Windows 2003/2000. What other info do you need?

Thanks in advance,
Sunny

 

[JtheRipper]

Hi,

Go to the properties of your linked server(security tab) and specify a username and password to connect to the linked server.

J.

 

[hencyt]

Thanks for the reply J. I have tried adding a user- but no matter what user I choose- even Admin- it always tells me the login failed or permission denied. Right now, no matter what login I put in, even when it isn't mine- the error says login failed for my user id.

Is it looking for a user with specific permissions?

Should I use the impersonate? Or the remote login? SO far I have tried both and neither have worked.

error 18456:Login failed for user

Thanks,
Sunny

 

[JtheRipper]

Hi,

You must use the login for the remote server you want to connect to in "remote user/remote password". Also, tick the "Be made using this security context and also use the remote user's credentials there (if you haven't already done so).

J.

 

[hencyt]

Thanks J!

I still couldn't get it to work from one server#1 to server#2, but I tried it vice versa and it worked.


Sunny