We have an old SQL Server 2000 database that has to be kept because it is needed on our manufacturing machines. It also maintains our employee records, since they are needed on these machines for employee logins.
We also have a newer SQL Server 10 database (I think this is 2008, but I'm not sure) that we are using for newer development.
I found a query I can use on SO to pull the records, but I must first link the two servers. Microsoft's information on Configuring Linked Servers did not help me much, either.
Following the SF post SQL Server to SQL Server Linked Server Setup, I tried adding the link.
In our SQL Server 2000 machine, I got this error:
Similarly, on our SQL Server 10 machine, I got this error:
The messages, though worded different, probably say the same thing: I need to authenticate, somehow.
We have an Active Directory, but it is on yet another server.
What, exactly, should be done here?
A guy >>HERE<< said to check the Security settings, but did not say what else to do. Both servers are set to SQL Server and Windows Authentication mode.
Now what?
EDIT: OK, how can I go about doing this, Tim?
Look at Administrative Tools -> Component Services MMC, Computers -> My Computer - Distributed Transaction Coordinator -> Local DTC -> Security.
I am remoted into the SQL Server 2000 machine, but all I see is this:
EDIT2:
So, I was not able to find this on our old Windows 2000 Server (running SQL Server 2000), but I did find this on our Windows 7 Server (running SQL Server 10).
I enabled the settings, let the service restart, and now I am on to other things.
Thanks Tim!