In one of the projects we are working on these days, there was a requirement to fetch some details, directly from the Oracle Database via VIEWS. Initially, everything was setup correctly on the Oracle Database & Server side so that we can access the relevant schemas and fetch data without any issue. And once the Oracle client is setup and the configurations are correctly setup (“tnsnames.ora”), we were able to fetch the details using .NET Code. And when we checked using the Oracle SQL Developer UI, it was evident that the details were easily fetched.
However, we faced an issue when we were asked to access and fetch the same set of details from SQL objects using OPENQUERY. Even when we try a simple query such as retrieving “sysdate”, we got an ‘Access Denied’ error.
SELECT * FROM OPENQUERY ([LINKED_SERVER], 'SELECT sysdate FROM DUAL')
The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied. Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"
After spending some time with the configurations on both SQL and Oracle side, we were able to rectify this issue by allowing “Allow inprocess” option in linked server providers in SQL side.
I am sharing this hoping that it would help someone to resolve the similar kind of issue without any hassle.
