Solved

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

Posted on 2008-06-23
3
831 Views
Last Modified: 2011-10-19
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
Comment
Question by:Vincent_Monaghan
3 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 21848038
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
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853045
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
 

Author Closing Comment

by:Vincent_Monaghan
ID: 31469810
Thank you
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question