Linked Server Connectivity Error SQL Server to Oracle

Question

I am trying to set up a linked server from SQL Server Production to one of our Oracle instances but experiencing this error :

The test connection in ODBC succeeds, but it fails on SQL Server side. I have checked the following things:

• OLEDB provider is installed
• OLEDB bit matches the bit of SQL
• OLEDB is registered
• Linked server properties is the last option to be able to login in using username and password
• Oracle provider has Allow In process checked
• Oracle ports are opened
• The right version client is installed

Are there anything else I should check further to resolve this error? Please provide me suggestions if you have experienced this before and what has worked for you. TIA

Answer 1

Hi @SQLLover21 ,

Welcome to Microsoft Q&A!

Please try below steps:

1. Run (CTRL + R) “dcomcnfg”. Navigate to “Component Services -> Computers -> My Computer -> DCOM Config”.
2. Open the properties page of “MSDAINITIALIZE”.
3. Copy the “Application ID” on the properties page.
4. Close out of “dcomcnfg”.
5. Run “regedit”. Navigate to “HKEY_CLASSES_ROOT\AppID{???}” with the ??? representing the application ID you copied in step #3.
6. Right click the “{???}” folder and select “Permissions”
7. Add the local administrators group to the permissions, grant them full control.
8. Close out of “regedit”.
9. Reboot the server.
10. Run “dcomconfig”. Navigate to “Component Services -> Computers -> My Computer -> DCOM Config”.
11. Open the properties page of “MSDAINITIALIZE”.
12. On the “Security” tab, select “Customize” under “Launch and Activation Permissions”, then click the “Edit” button.
13. Add “Authenticated Users” and grant them all 4 launch and activation permissions.
14. Close out of “dcomcnfg”.
15. Find the Oracle install root directory. “E:\Oracle” in my case.
16. Edit the security properties of the Oracle root directory. Add “Authenticated Users” and grant them “Read & Execute”, “List folder contents” and “Read” permissions. Apply the new permissions.
17. Click the “Advanced Permissions” button, then click “Change Permissions”. Select “Replace all child object permissions with inheritable permissions from this object”. Apply the new permissions.
18. Find the “OraOLEDB.Oracle” provider in SQL Server. Make sure the “Allow Inprocess” parameter is checked.
19. Reboot the server.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.