Re: SQL linked server

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0406041520.3c2804f6_at_posting.google.com...
> "Rob Oldfield" <rob_at_oldfield100_wow_freeserve_yikes_co_incredible!_uk>
wrote in message news:<40c0ba39$0$4593$db0fefd9_at_news.zen.co.uk>...
> > 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?
>
> Are your init.ora files the same on both machines (especially
> OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT... no wait, those are O8.) ?
> It sounds kinda like and OPS$ problem, maybe the new one disallows
> that, or remote connections? Also, check if there is a connect string
> somewhere in the registry and try to connect with that. A connect
> string might look something like a username, slash, password, at sign,
> database SID, like scott/tiger_at_orcl. You should be able to do that
> from a dos box. Check the database for usernames, if you see stuff
> like OPS$ prepended to usernames that means the database is using the
> OS capability for deciding who is logged on, and a connection string
> might look like /@orcl IIRC (it's been years since I've done this).
> Or if you have the user creation code, look for "identified
> externally" as opposed to identified by some password.
>
> Of course, it might just be the ODBC isn't supported with such an old
> product on such a new platform.
>

Thanks to both of you for the responses. It turns out that I had things slightly wrong and that the database itself is v 8.1.7 - it's just the 7.3.4 client version we're using (for some reason). There seems to be enough out on the web about this error under 8.1.7 to keep me going for a while.

Thanks again. Received on Mon Jun 07 2004 - 13:08:13 CDT