Results 1 to 12 of 12

Thread: Linked Server problem

  1. 03-17-2008, 01:40 PM #1
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22

    Linked Server problem

    Can anyone help me out with this problem?

    I had a linked server created with Oracle Provider for OLE DB to Oracle Database (9i) from SQL Server 2000 SP 4 - the linked server was mapped to a single oracle user. I had views created over this linked server for my sql server users. This connection have been working perfectly until recently when I started to receive intermittent breaks which required sql server reboot for the connection to be reinstalled. I could not solve this problem so I decided to use other providers for the linked server.

    I tried both Microsoft OLE/DB Provider for ODBC Driver and the native Oracle Provider for OLE DB. In both cases I could access the views without problem (my login is also a member of the administrator group on the server where the sql server is installed).
    When my users tried to access the views the following errors was reported:
    From the MSDASQL provider:
    [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].(#7300)[Microsoft][ODBC SQL Server Driver][SQL Server] OLE DB provider 'MSDASQL' reported an error. Authentication failed. (#7399)

    From the native Oracle Provider:
    [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Error while trying to retrieve text for error ORA-12154](#7312)[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB provider 'OraOLEDB.Oracle' IDBInitialize::Initialize returned 0x80004005: ]. (#7300)[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'OraOLEDB.Oracle' reported an error. (#7399)

    Please help. I've spend over 4 days searching for solutions from the web while my users are blowing their heads off

    Victor
    Reply With Quote Reply With Quote
  2. 03-17-2008, 03:26 PM #2
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Did you map all sql users to that oracle user in linked server?
    Reply With Quote Reply With Quote
  3. 03-17-2008, 04:52 PM #3
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22
    Not individually - globally for all sql server users
    Reply With Quote Reply With Quote
  4. 03-18-2008, 08:16 AM #4
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    How did you do that?
    Reply With Quote Reply With Quote
  5. 03-18-2008, 03:12 PM #5
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22
    sp_addlinkedsrvlogin mylinkedserver, FALSE,NULL, oracleuser, oracleuserpassword.

    The same method was used for servers linked with Microsoft OLE DB Provider for Oracle which had been working correctly
    Reply With Quote Reply With Quote
  6. 03-19-2008, 08:35 AM #6
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Can you double check in linked server properties -> security?
    Reply With Quote Reply With Quote
  7. 03-19-2008, 01:26 PM #7
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22
    Checked.

    All logins not defined are configured to use remote login with password (defined with sp_addlinkedsrvlogin).
    No local login is defined or individually mapped.
    Reply With Quote Reply With Quote
  8. 03-19-2008, 02:58 PM #8
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    You are sysadmin? What kind of sql permission other users have? Any other sysadmin has that error?
    Reply With Quote Reply With Quote
  9. 03-20-2008, 01:39 AM #9
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22
    Yes I'm a sysadmin. The sql users have both read and write permission to the database where those views are created. No other sysadmins has this problem.

    Please note that there was no such problem when the linked servers were created using Microsoft OLE DB Provider for Oracle (under the same conitions)
    Reply With Quote Reply With Quote
  10. 03-20-2008, 08:44 AM #10
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Each provider works differently, seems permission issue here.
    Reply With Quote Reply With Quote
  11. 03-22-2008, 01:16 PM #11
    vicoluf
    vicoluf is offline Registered User
    Join Date
    Sep 2004
    Location
    Poland
    Posts
    22
    Quote Originally Posted by rmiao
    .. seems permission issue here.
    Okay, but which permission issues are we talking about?

    I try to reproduce the error on vmware servers but could not. All the 3 providers worked as expected
    Reply With Quote Reply With Quote
  12. 03-22-2008, 02:57 PM #12
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Permission in that sql instance. I found on my server that rebooting can solve such issue.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules