problem with a linked server query and different 'sa' passwords

[h0gg1e]

I have a two sql servers that have been sucessfully linked, using sp_addlinkedserver. The problem is server1 has a blank 'sa' password and server2 has a 'sa' password. When ever I try to run a linked query:

SELECT Distinct PONumber, OrderNumber, LoadNumber, CarrierID
from tblOrderMasterfile
where OrderStatus='IN PROCESS'
AND PlannedShipDate='12/20/04'
AND LoadNumber
NOT IN (SELECT LoadNumber
FROM server1.PWMS.dbo.tbl_DockSchedules
WHERE LoadNumber IS NOT NULL)
ORDER BY PONumber, LoadNumber


I get an error that says login failed for 'sa' :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].



When I set up the linked server I set it up NOT using a different login than 'sa' :

EXEC sp_addlinkedserver
@server = 'server1',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=server1;UID=user;PWD=test;Initial Catalog=PWMS'

Is there anyway to specify which user the server uses to do linked queries or anyway to allow me to run a linked query with different 'sa' passwords?

Thanks SO much for anyhelp, because this one is making me scratch my head.

As a side note, I know the linked query works because it works against two servers that both have a blank password.

 

[h0gg1e]

I figured it out using:

EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'user', 'test'

 

[SQLBill]

Please patch your systems. Make sure they have the latest SP and give the SA login a strong password. Have you never heard of SLAMMER worm (aka SAPPHIRE)?? The latest SP for SQL Server 2000 is SP3a.

-SQLBill