I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...
After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:
> I've tested a link server from msde to oracle on a win2k server test machine
> and after some effort, it worked properly. I noted down the steps i took to
> get it to work. I could enable or disable AllowInProc for the Microsoft OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i am
> kind of running out of ideas right now...
|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...
|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David
|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then i
suspect we'd have to be running it in-process.
"David Browne" wrote:
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>
|||I just tested using an ODBC data source using the Microsoft ole db for Oracle
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
[vbcol=seagreen]
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got it
> to work. It seemed even more trouble than the microsoft driver. Even then i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
[vbcol=seagreen]
> I just tested using an ODBC data source using the Microsoft ole db for Oracle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is) ?
> the linked server was set up to use an odbc source, a source which used the
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment