Setting up SQL Server 2014 and Oracle 12c Linked Servers

Running multiple SQL Server and Oracle database platforms to support different applications is the norm for many IT organizations today. While in many cases the two different database platforms often operate as independent islands there are also times you might need to integrate the two database platforms to perform lookups, queries and other operations that incorporates data from the other database platform. If your SQL Server database needs ad-hoc database access to Oracle the answer is Linked Servers. SQL Server’s Linked Servers allow you to seamlessly integrate your SQL Server applications with the tables, indexes and views stored in an Oracle database. In this article I’ll cover some of the requirements and configuration for setting up a SQL Server 2014 linked server to Oracle 12c.

Oracle Linked Server Requirements

Before creating a SQL Server 2014 linked server to Oracle you need to install the Oracle Data Access Components onto the SQL Server 2014 system. The Oracle Data Access Components software provides the network libraries that are required to establish connectivity to the Oracle database for the SQL Server system. To install the Oracle Data Access Components download the ODAC121021Xcopy_x64.zip file from 64-bit Oracle Data Access Components (ODAC) Downloads. Then use an elevated command prompt to run the install.bat file to install the data access components. The following example illustrates how to install all of the Oracle Data Access Components into the c:\oracle directory:

C:\temp\odac121021xcopy_x64>install.bat all c:\oracle odac

Next, per the readme.htm file I added c:\orcale and c:\oracle\bin to the system PATH variable.

Configuring Linked Servers

Once the Oracle Data Access Components have been installed you can configure the SQL Server linked server. To configure the linked server open SSMS then expand the Server Objects node. Next expand the Linked Server node and the Providers node. You should see a OLE DB provider named OraOLEDB.Oracle. Right click the provider and select Properties from the context menu to display the Provider Options dialog that you can see in Figure 1.

 

Figure 1. Configuring the Oracle OLEDB Provider

 

In the Provider Options check the Allow inprocess checkbox and then click OK. This will allow the provider to run in the SQL Server process. At this point you’re ready to create the new Oracle linked server. You can create a new linked server using either T-SQL or SSMS. It this example I’ll use SMSS and will show you the equivalent T-SQL commands at the end.

To create a new linked server right click the Linked Servers node in SSMS and select New Linked Server from the context menu. This will display the New Linked Server dialog that you can see in Figure 2.

 

Figure 2. Creating the Linked Server

 

First give the linked server a name. The linked server name is a descriptive name and can be any valid SQL Server name. In Figure 2 you can see that I chose the system name of LINK_ORACLE12C. Next, select the OLE DB provider that you want to use from the Provider dropdown list. In Figure 2 you can see that I selected the Oracle Provider for OLE DB that was installed as a part of the Oracle Data Access Components. For the Product name text box I used the name of OraOLDB.Oracle – the name of the OLE DB provider. In the Data source text box I used the Oracle EZConnect string – pointing to the host and I omitted the optional port and service names. In Figure 2 you can see that I used the name TPOTORC12C. Click the Security link on the left to open the Security page that you can see in Figure 3.

 

Figure 3. Linked Server Security

 

You have several options for authenticating the linked server connection to the Oracle database. The best options are typically either to map local SQL Server logins to Oracle logins or to use current security context. To map SQL Server logins to Oracle logins, click the Add button and enter the login credentials for each system.

The example shown in Figure 3 uses the Be made using this security context option to map all linked server queries to the Oracle scott/tiger login, which has access to the sample SCOTT database. The Be made with the login’s current security context option works best when the Oracle system is using Windows authentication.

The following commands show the T-SQL statements that can create this SQL Server linked server.

 

EXEC master.dbo.sp_addlinkedserver @server = N'LINK_ORACLE12C', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'//TPORTORC12C'



EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINK_ORACLE12C',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

In this article you’ve seen how to create SQL Server Linked Servers that connect to Oracle. In the follow-up article I’ll give you some tips and tricks about using SQL Server to Oracle linked servers.