Setting up SQL Server 2014 and Oracle 12c Linked Servers

By: Michael Otey


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.

 MOtey Linked Servers Fig 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.

 

MOtey Linked Servers Fig 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.

 

MOtey Linked Servers Fig 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.

Comments

  1. I’ve attempted this several times on a SQL2014 server connecting to Oracle 10g using the 12c 64 bit drivers. It connects but returns an error when attempting to pull data. The error is:

    Msg 7356, Level 16, State 1, Line 22

    The OLE DB provider “OraOLEDB.Oracle” for linked server LinkedServerName supplied inconsistent metadata for a column. The column “SPECIFIED” (compile-time ordinal 12) of object TABLE_NAME was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.

    The column in question is a CHAR(1) column.

    If I pull the data using OPENQUERY I get 1 row returned and the following errors:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider “OraOLEDB.Oracle” for linked server LinkedServerName reported an error. The provider reported an unexpected catastrophic failure.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider “OraOLEDB.Oracle” for linked server LinkedServerName.

  2. Hi, I was able to create the linked server. It’s all ok when talking about database.

    The problem is that when I’m on the web server trying to use it says “cannot initialize data source object of ole db provider OraOLEDB Provider for linked server”

    I look everywhere for an answer from google to seniors.

    The workflow is:
    1) Codebehind executes store procedures
    2) Store Procedure executes a DTS
    3) DTS executes a store procedure
    4) Store Procedure make SELECT querys to the linked Oracle Database

    Thanks for your help!

  3. As soon as I set “Provider” to “Oracle Provider for OLE DB” the “New Linked Server”-Dialog doesn’t respond anymore 🙁
    I’m running SQL 2014 on Win 2012 R2. Oracle Drivers are 11g.
    Any Ideas?

Leave a Reply