Linked Server problem

Printable View

03-17-2008, 01:40 PM
vicoluf

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
03-17-2008, 03:26 PM
rmiao

Did you map all sql users to that oracle user in linked server?
03-17-2008, 04:52 PM
vicoluf

Not individually - globally for all sql server users
03-18-2008, 08:16 AM
rmiao

How did you do that?
03-18-2008, 03:12 PM
vicoluf

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
03-19-2008, 08:35 AM
rmiao

Can you double check in linked server properties -> security?
03-19-2008, 01:26 PM
vicoluf

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.
03-19-2008, 02:58 PM
rmiao

You are sysadmin? What kind of sql permission other users have? Any other sysadmin has that error?
03-20-2008, 01:39 AM
vicoluf

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)
03-20-2008, 08:44 AM
rmiao

Each provider works differently, seems permission issue here.
03-22-2008, 01:16 PM
vicoluf

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
03-22-2008, 02:57 PM
rmiao

Permission in that sql instance. I found on my server that rebooting can solve such issue.