SQL linked server

I have a very strange situation. I currently have a copy of SQL 2000 running on a Windows 2000 box which has a linked Oracle server included. And that's fine.

I'm now upgrading to SQL 2000 running on a 2003 server, but when I come to add the linked server in then it's failing. "Error 7399: OLE DB provider 'MSDAORA' reported an error. Authentication failed. OLE DB error trace
[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.]."

If I go to my original SQL box and add the new server in to Enterprise Manager then it works fine. If I then go back to the new machine and attempt to view any info in the linked tables, then it fails. If I go to the new server and check the connection using the name of the connection I've used in SQL net then that's also fine.

If I go to the old server and add a new view into a database pulling info out of Oracle, then it works fine. If I then save that view, go back to the new server, and try running that view then I get...

[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: ORA-01004: default username feature not supported; login denied]

I'm logged in to both machines as the domain admistrator, both machines have the same version of the client software installed and both servers are using the same domain account to run the sql service. The Oracle database is, by the way, version 7.3.4

I have, by the way, tried posting this onto SQL newsgroups, but nobody seems to have a clue there.

Any ideas? Received on Fri Jun 04 2004 - 13:10:23 CDT