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.
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports
Submit an article or tip
