Problems with linked Servers

Both servers run windows server 2003 and sql server 2000.
Both servers have a link to the other server under linked servers using sql authentication.
Both servers have windows and sql authentication enabled.

The problem I am having is despite both servers being linked exactly the same I can only view server A from server B. If I log into server A I can not use the linked server setting to server B to view data.

I get the following error.

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'updater'. Reason: Not associated with a trusted SQL Server connection.

I searched many forums and they all seem to point to sql authentication not being enabled however this is not true of my 2 servers. Both have and always have had windows & sql server authentication.

Any help with this problem would be gratefully recieved.

From the error message, it does look like a SQL Authentication attempt coming from Server A to Server B, while Server B is set to accept only Windows authenticated logins; however you're saying Server B is configured to accept both SQL and Windows authenticated connections. So this is a mystery to me.

Can you please check the login mappings are set up correctly from ServerA to ServerB?

If you execute in Server A:

Sp_helplinkedsrvlogin

it will give all the login mappings set up in server A; you should either have an explicit mapping for the ServerA login to ServerB with a valid SQL login on ServerB and the password.

Thanks,

- Balaji

|||

DATADISS2 NULL 1 NULL

DATADISS3NULL 0 updater

DATADISS2NULL 0 updater

DATADISS3 NULL 1 NULL

Both users are mapped correctly and I checked again for authentication. The problem is I can not get datadiss2 to access datadiss3.

http://imagebin.org/4016

http://imagebin.org/4017

I have put 2 screen shots of the security tab from enterprise manager to make sure they are set correctly.

|||I created 2 connections inside a dts package and ran both sql scripts from there and the only error I occured upon was that I didnt have permission to create a table. datadiss2 user updater required database owner status where as datadiss3 user updater only required datawriter/datareader status is a bit werid but it ran and seemed to work. When I then ran the code from query analyser it works. I am not sure what it is I have altered or why it has started working but it works.