Oracle Linked server / opendatasource

I am trying to select from an Oracle 9i table and get error below. I have
tried both linked servers and OPENDATASOURCE both get the same error. The
opendatasource syntax is also listed below.
The user I am using definitely has permissions to the database/table as I
can pull the information with a DTS package using the same credentials.
Any help would be great.
Thanks
Gary
Error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
IUnknown::QueryInterface returned 0x80070005: Access denied.].
Syntax
SELECT *
FROM OPENDATASOURCE(
'OraOLEDB.oracle',
'Data Source=MyOracleDB;User ID=user;Password=password'
).MyOracleDB.schema.table
What driver are you using? We use both the MS OLEDB Provider for Oracle and
the MS OLEDB Provider for ODBC and don't have problems using linked servers.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
> I am trying to select from an Oracle 9i table and get error below. I have
> tried both linked servers and OPENDATASOURCE both get the same error. The
> opendatasource syntax is also listed below.
> The user I am using definitely has permissions to the database/table as I
> can pull the information with a DTS package using the same credentials.
> Any help would be great.
> Thanks
> Gary
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
> Syntax
> SELECT *
> FROM OPENDATASOURCE(
> 'OraOLEDB.oracle',
> 'Data Source=MyOracleDB;User ID=user;Password=password'
> ).MyOracleDB.schema.table
>
|||I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
tools installed on the box.
"michelle" <michelle@.nospam.com> wrote in message
news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> What driver are you using? We use both the MS OLEDB Provider for Oracle
and
> the MS OLEDB Provider for ODBC and don't have problems using linked
servers.[vbcol=seagreen]
> "Gary Hindson" <garyhindson@.hotmail.com> wrote in message
> news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
have[vbcol=seagreen]
The[vbcol=seagreen]
I
>
|||We had trouble with that driver. We also had the client tools installed. I'm
sorry that I can't help you any further.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OGH7Mj3fEHA.3024@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
> tools installed on the box.
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> and
> servers.
> have
> The
as[vbcol=seagreen]
> I
credentials.
>
|||Gary Hindson <garyhindson@.hotmail.com> wrote:

> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
I had a similar problem recently using any OLE DB provider with MSDE. It
produced an identical error message to yours, except the provider was MSDASQL
(the one for ODBC drivers.)
Adding this to the registry fixed it:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000
If you're using SQL Server 2000 rather than MSDE it will already have these
registry keys in. Looks like one is needed per provider or it won't work.
Try adding whatever is needed above, and this addional line for your Oracle
driver and see what happens:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle]
"AllowInProcess"=dword:00000001
If it isn't the default instance of SQL Server look at these registry keys
instead:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
where <#> is the name of your instance.
Steve
|||Thats works a treat.
Thanks for your help
"Steve Hunter" <FIRSTNAME.LASTNAME@.penn-elcom.com> wrote in message
news:411cd5c5@.nntp.onyx.net...
> Gary Hindson <garyhindson@.hotmail.com> wrote:
>
> I had a similar problem recently using any OLE DB provider with MSDE. It
> produced an identical error message to yours, except the provider was
MSDASQL
> (the one for ODBC drivers.)
> Adding this to the registry fixed it:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
> "AllowInProcess"=dword:00000001
> "DisallowAdhocAccess"=dword:00000000
> If you're using SQL Server 2000 rather than MSDE it will already have
these
> registry keys in. Looks like one is needed per provider or it won't work.
> Try adding whatever is needed above, and this addional line for your
Oracle
> driver and see what happens:
>
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle
]
> "AllowInProcess"=dword:00000001
> If it isn't the default instance of SQL Server look at these registry keys
> instead:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
> where <#> is the name of your instance.
> Steve
>