I'm having an issue with a linked server on MSSQL Sever 2016.
I created a link to an Oracle server, TKCS. It uses the Oracle OLEDB drivers installed by the Oracle client (11g). On the security tab of the linked server I added no mappings and set a default security context to a remote user/password. Testing the connection shows success. I can access it and pull data as expected under the remote user set in the security context. All this is as it should be.
My co-worker, can't use the linked server and gets the following error:
SQL Server Database Error: Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "TKCS".
We can't see a reason for this. My understanding is that with the Oracle OLEDB drivers installed on the sql server there should be no need for them locally. I don't think its permissions because, as I understand things, the security context defaulting to the same remote user for everyone means that everyone should have the permissions that login has.
Has anyone run into this problem, how did you fix it?