Within SQL it is possible to create a Linked Server to connect to an Oracle database server.
In previous versions of SQL, this was done by using the OLE DB Provider for Oracle. But this is this component is currently in maintenance mode. No new updates are planned for future releases of these components including their support against versions later than Oracle 8i. Read more.
Nowadays we can use the Oracle Provider for OLE DB to setup connections to an Oracle server from SQL server.
- Download and Install the Oracle x64 client
- Check if the System Environment Variable %ORACLE_HOME% is set to the installation directory of the Oracle client. If not, create the variable.
- Modify the TNSNAMES.ora file in the installation folder of the Oracle client so it contains the information about the Oracle server.
- Download and Install Oracle ODAC x64 .
This creates a OraOLEDB.Oracle provider in SQL Management Console under Server Objects, Linked Servers, Providers and enables the Oracle Provider for OLE DB option in the provider drop-down menu when creating a new Linked Server to Oracle.
- Create a new Linked Server (make sure Step 2 and 3 are completed)
- Enter the name for the Linked Server.
- Select Oracle Provider for OLE DB from the drop-down menu
- The Product Name and Datasource is the description of the sever in TNSnames.ora
- On the Security tab, select Be made using the security context and enter the username and password of the user that has permissions on the Oracle database
- Leave the Server Options default and click Ok
- Right click on the new Linked Server and click on Test Connection
- The Linked Server can now be used to access the Oracle Database server.