Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Unanswered: oracle as linked server

    Hi,

    i want to set Oracle as a linked server to my MS SQL.
    When I execute

    EXEC sp_addlinkedserver
    @server = 'ORATEST',
    @provider='MSDAORA',
    @srvproduct='any',
    @datasrc='ORADB_F9.SAPIENS.COM'

    and then trying to make sample select, I get the following:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDAORA' reported an error. Authentication failed.
    [OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied]
    OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

    Please advice, where can I set up the username and password for my Oracle, so the things will work.
    --
    kukuk

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to books online for "Establishing Security for Linked Servers" topic for information. Make sure to use the same user where it does have privilege to access Oracle tables.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Satya,


    Thank you very much - I've forgotten about it.

    Now the next question:
    I did

    sp_addlinkedsrvlogin
    @rmtsrvname = 'ORATEST',
    @useself = 'false',
    @rmtuser = 'hana',
    @rmtpassword = 'hana'

    Now, when I run the following query:

    select * from ORATEST...deptsales

    , it says me the following:

    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'ORATEST' does not contain table 'deptsales'. The table either does not exist or the current user does not have permissions on that table.
    OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ORATEST', TableName='deptsales'].

    I am sure that the table does exist, I could see it via Excel.
    --
    kukuk

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    May 2003
    Posts
    25
    Originally posted by Satya
    Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.
    I am having this same error.. I am not sure what you mean by mapping local login to remote login. Where do we access the name of our remote login??

  6. #6
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Originally posted by Satya
    Error is self-explanatory, when setting up the linked server map your local login to appropirate remote login.
    I did it, as you can see from my previous post - did I do something wrong ?

    I've created an antry for Oracle's login "hana". I am sure that the table "hana.deptsales" exists. What's wrong here ?

    TIA
    --
    kukuk

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    How about privileges for the user Hana on that table?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Hana is the table owner
    --
    kukuk

  9. #9
    Join Date
    Jun 2003
    Posts
    31
    You forgot to mentioned the Database Name. The Oracle's query syntax should be like:

    select * from SERVERNAME.DatabaseName.Owner.TableName

    SVT

  10. #10
    Join Date
    Jun 2003
    Posts
    31
    Let me explain further the steps that you needed to do before query the Oracle database.

    1) You'll need to install the Oracle client software on the SQL Server,
    and reboot the server for the Oracle's DLL to register.
    2) Run the
    sp_addlinkserver 'TestOracle', 'OLEOracle', 'MSOracle', 'TSTORA'
    (where:
    'TestOracle' is the linkserver name
    'OLEOracle' is the OLE DB Name
    'MSOracle' is the Microsoft's OLE DB
    'TSTORA' is the Net Service Name (create with Oracle NetConfig
    Assistant)

    3)Mapping Logins
    EXEC sp_addlinkedsrvlogin 'TestOracle', FALSE*, Null, 'Scott', 'Tiger'

    (Add "scott' with pwd as "tiger' on 'TestOracle', if * is set to TRUE means SQL Server will use the CURRENT login to connect to the link server, use "TRUE' only if the Logins of the 2 DB servers matched).

    4) Test the connection. From the QA:

    Select * from TESTOracle..Scott.EMP


    Good luck,
    SVT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •