Linked Server - Get 'Login failed...'

[JohnBates]

Hi experts,

Let me describe the situation...this is SQL Server 2000

Server 1 uses Windows NT authentication.

Server 2 uses SQL Server authentication and has Server 1 as a Linked Server.

****************************************
On Server 2, if I try to query a table that resides on Server 1 (Select * From Server1.db.dbo.tablename), I get a login error 'Login failed: MEM1\ACB_SQLServerAgent is not associated with a trusted SQL Server connection'
****************************************

Both servers have a login named MEM1\ACB_SQLServerAgent.

What else do I need to do to allow users on Server 2 to access data on Server 1?

Thanks for your ideas. John

 

[JayKusch]

You could, if permisable, change the authentication from strictly NT to Mixed Mode on server1. If you are not familiar w/ how to accomplish this, just open Enterprise Manager, right-click on the Server Name and choose Properties..Security. Then Change the mode from "Windows Only" to "SQL Server and Windows".

Thanks

J. Kusch

 

[JohnBates]

Thanks, JayKusch.

I may have to change Server1 to mixed mode authentication. But that will make my job (DBA) more difficult in that I will have to give permissions on certain tables at the db level for certain users - am I correct ?

Isn't there an "impersonate" feature? Has anyone used that?

Again, this is Windows 2000 Server and SQL Server 2000.

Thanks, John