July 16, 2003 at 8:49 am
Hello - I created a linked server to Oracle, but when I try to run a simple select on a table, I get this error message in Query Analyzer:
--------------
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface returned 0x80070005: Access denied.].
--------------
Here is the sql that I used to create the linked server and login. I have verified that my user name and password do have access via SQLPlus. And I have made the necessary registry fixes that MS recommends @ http://support.microsoft.com/default.aspx?kbid=264012
--------------
USE master
go
EXEC sp_addlinkedserver @server='MyOracleDB', @srvproduct='Oracle', @provider='MSDAORA', @datasrc='MyOracleDB', @location='', @provstr='Provider=MSDAORA.1;Data Source=MyOracleDB;Persist Security Info=True'
go
EXEC sp_serveroption @server='MyOracleDB', @optname='rpc', @optvalue='false'
go
EXEC sp_serveroption @server='MyOracleDB', @optname='collation compatible', @optvalue='false'
go
EXEC sp_serveroption @server='MyOracleDB', @optname='data access', @optvalue='true'
go
EXEC sp_serveroption @server='MyOracleDB', @optname='rpc out', @optvalue='false'
go
EXEC sp_addlinkedsrvlogin @rmtsrvname='MyOracleDB', @useself='FALSE', @locallogin=NULL, @rmtuser='MyUserName', @rmtpassword='MyPassword'
go
--------------
Does any one have any other options I can try? I am at my wits end with this.
Cheers
- Andy
July 16, 2003 at 9:10 am
Do you have Oracle client (SQLNET) installed in your SQL Server? Check your tnsname.ora file too.
July 16, 2003 at 9:19 am
From BOL.
"To create a linked server to access an Oracle database instance
Ensure the Oracle client software on the server running SQL Server is at the level required by the provider. The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version 7.3.3.4.0 or later, and SQL*Net version 2.3.3.0.4.
Create an SQL*Net alias name on the server running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation.
Execute sp_addlinkedserver to create the linked server, specifying MSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.
This example assumes that an SQL*Net alias name has been defined as OracleDB.
sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB'
Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.
This example maps the SQL Server login Joe to the linked server defined in Step 3 using the Oracle login and password OrclUsr and OrclPwd:
sp_addlinkedsrvlogin 'OrclDB', false, 'Joe', 'OrclUsr', 'OrclPwd'
Each Oracle database instance has only one catalog with an empty name. Tables in an Oracle linked server must be referenced using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName. For example, this SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:
SELECT *
FROM OrclDB..MARY.SALES"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply