I setup a linked server on a new database server and was trying to run a query that runs against an Oracle database and I got the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server “ccbtst” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “OraOLEDB.Oracle” for linked server “ccbtst”.
I have set this up on other servers, what did I forget to do?
To troubleshoot, first I open up the provider. In this case, it was the OraOLEDB.Oracle provider.
I forgot to enable Allow inprocess.
Here it is correctly enabled.
What does Allow inprocess do? From the documentation:
"allows the provider to be instantiated as an in-process server. When this option is not set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed."
To read more about the settings available for linked servers, the documentation is a good read: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15
Now I attempt to run my query, and it succeeds.