1

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:

SQL2000 Error

Similarly, on our SQL Server 10 machine, I got this error:

SQL10 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:

Component Services Snap

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).

Somponent Services 7

I enabled the settings, let the service restart, and now I am on to other things.

Thanks Tim!

Improve this question

2 Answers 2

Reset to default
1

Configuring linked servers / distributed transactions within SQL 2000 can be a bit of a pain since SQL 2000 only supports anonymous configurations. This is a known limitation.

Look at Administrative Tools -> Component Services MMC, Computers -> My Computer - Distributed Transaction Coordinator -> Local DTC -> Security.

Enable Network DTC access, allowing remote clients and remote administration. Set the 'No Authentication Required' box for transactions.

That should do it.

Improve this answer
3
  • I was not able to find Component Services MMC, Computers, only Component Services. (See my updated question)
    – jp2code
    Jun 25, 2012 at 16:14
  • @jp2code - It is Compnent Services. MMC is the file type. Sorry about the confusion.
    – Tim Brigham
    Jun 25, 2012 at 16:26
  • @jp2code - the security is a right click context menu.
    – Tim Brigham
    Jun 25, 2012 at 16:27
0

I think the easiest way to handle this would be to set up 2 SQL logins (logina and loginb) on each server (servera and serverb) and create a linked server on each to the other. On servera you change the properties of the linked server (serverb) and go to the security tab. Change the radio button to "be made using the login's security context" and then put the un/pw for "loginb" in the "remote login" and "with password" options.

On serverb you change the properties of the linked server (servera) and go to the security tab. Change the radio button to "be made using the login's security context" and then put the un/pw for "logina" in the "remote login" and "with password" options.

This will allow you to make queries from serverb to servera using the credentials of logina and vice versa. From there you just need to grant the rights to the user on server1 and serverb.

The downside of this approach is that anyone with access to serverb will be able to change the data on servera that logina has access to and the other way around, but the configuration should be simpler than using component services. If you limit the grants to just read this will not be an issue.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .