question

SQLLover21-0870 avatar image
0 Votes"
SQLLover21-0870 asked CarrinWu-MSFT commented

Linked Server Connectivity Error SQL Server to Oracle

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

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


sql-server-general
1.png (121.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Rather than clicking buttons, try to run a query in a query. There may be an informational message which gives more details of what is going on.

0 Votes 0 ·

Hi @SQLLover21-0870, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

1 Answer

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @SQLLover21-0870,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.