SQL Server error - The linked server has been created but failed a connection test. Do you want to keep the linked server?
Linked Server errors, such as between SQL Server 2000 and 2005 versions are quite common in DBA-day life.
When it goes wrong I would always refer (myself) and others to review HowDoI-PreventLinkedServerIssues resource from ASPFAQ site. Also the usual practice of checking of whether its a security issue to allow the logins, if so then goto the Security page (still on Linked Server Properties). Select 'Be made using this security context'. Type a login and password that exists on the remote server. Further KBA906954 - [You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server] talks much on the symptoms you need to consider and manual execution of script.
The initial connection on the Linked Server will initiate xp_prop_oledb_provider to check whether the referred be-made login context matches with the security on defined server instance. I'm not sure whether using a SQL Server 2000 version linked server will check for such a security-context when you create a linked server, but within SQL Server 2005 version it will always test the connection when executing sp_testlinkedserver. This doesn't mean all of the Linked Server permissions are checked by this stored procedure, as no permissions are checked; however, the caller (login) must have the appropriate login mapping. Similarly if the account that is used to create this linked server is not a part of SYSADMIN role while performing "local server login to remote serer login mappings:" list, then the following message appears:
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)
When such a linked-server problem has occurred at a client's place there was no direct resolution even though after checking all the permissions and login privileges are intact. Also checked whether any Anti-Virus or Anti-spyware application that is blocking any ports on the server 1433, which is a default for SQL Server liste-on. Further you need to ensure when a Windows XP client PC is associated, as there are many changes from XP Service Pack 2 and I recommend to check this KBA841249 that will help to configure XP SP2 for use with SQL Server instances. This should resolve all the issues associated with Linked Server setup & configuration.