• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

How to create a linked server in MS SQL Server 2005 which links to an MS SQL Server 2000 server

I am trying to create a linked server in MS SQL Server 2005 which links to an MS SQL Server 2000
server. I am using the wizard and have attached the details input into the popup screen and a copy of the error message.

One problem is that the client has lost the password for the sa login to SQL Server 2000, so I have to login using 'windows authentication'.
LinkSVR.JPG
Err.JPG
0
Vincent_Monaghan
Asked:
Vincent_Monaghan
1 Solution
 
MikeWalshCommented:
You are trying to connect to that 2000 instance with whoever is running the SQL Server Service on the 2005 machine.

That login will need to have permission to connect and perform the necessary actions (Be a login, connect to that database, read/write tables exec procs, etc.)

Another option is to use an existing account and click on the security tab and provide a different remote login/password. It doesn't have to be SA it can be any user with the necessary permissions.

If you select "Be Made Using the Login's Current Security Context" for the default connections (For a login not defined in the list above connections will:.. third choice) it should also allow you to connect to the 2000 server with the authentication you have. So if you have proper permissions on each server and select that option you should be good also.
0
 
Jagdish DevakuSr DB ArchitectCommented:
HI,

Please refer to the following link which might help you in resovling the issue...

http://sequelserver.blogspot.com/2007/06/under-default-settings-sql-server-does.html

All the best...
0
 
Vincent_MonaghanAuthor Commented:
Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now