I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and 64bit oracle odbc drivers. If I run the below query with Windows authentication it is giving me the below error. But surprisingly if I run it as SQL Account it is running perfectly fine without any errors.
I think the error message is misleading but I can't think of a reason why the linked server would work for SQL Account and not for Windows authentication.
SELECT USR_LGN_CD, USR_STS_IND
FROM OPENQUERY(cwcin11d,'SELECT * FROM ccwas_usr' )
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "cwcin11d" reported an error. Access denied.
Msg 7332, Level 16, State 2, Line 1
Cannot rescan the result set from OLE DB provider "OraOLEDB.Oracle" for linked server "cwcin11d". Unknown provider error.