Msg 7399, Level 16, State 1, Line 1 / Msg 7303, Level 16, State 1, Line 1 in MS SQL Server dblink

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST".


Solution:

The solution to troubleshoot error is to set OraOleDB.Oracle provider Allow inprocess option.In order to configure Allow inprocess option goto OraOleDB Oracle provider properties.

Be sure that the Allow inprocess option is marked.

Allow inprocess

SQL Server 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.

Now execute the following sql statements on Oracle databases using SQL Server Linked Server.

SELECT
 [FIRST_NAME], [LAST_NAME]
FROM [ORCL_TEST]..[HR].[EMPLOYEES]

step by step to create dblink in MS SQL database to connect Oracle Database

Step1: Download Oracle client

Step2: Install Oracle Client in the server where MS SQL Database installed 

Step3: Configure tnsnames.ora file in Oracle client

Step4: Start SQL Server Management Studio

Step5:  Enter the login name and password for a SQL administrator account such as sa. 

Step6: Click to expand the SQL Server | Server Objects.
Right-click Linked Server and click New Linked Server.

 Step7 : Create a SQL Server Linked Server Object

1. Start SQL Server Management Studio
2. Expand Server Objects
3. Expand Linked Servers
4. Expand Providers

5. Make Sure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers) is installed on the server
6. Right Click on Linked Servers
7. Select New Linked Server...
8. Assign a name to the Linked Server; I suggest a single word name like oracle_db.
9. Select Microsoft OLE DB Provider for Oracle
10. Set the Product Name to Oracle Database.
11. Set the Data Source to the tnsname

12. Select Security page
13. Select Be made using this security context
14. Set the Remote login field to the Oracle Database User Id
15. Set the With password field to the Oracle Database Password

16. Click OK button

Step8 : Test the new Linked Server