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.
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.
I’m happy that you got this working. There were significant changes to the Oracle drivers between 10g and 12c.
I have the same issue with SQL 2016 Standard and Oracle 12c.
How did you finally solve the Problem?
Nicolás Germone says
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!
Do you need all of the components in the ODAC or just the odbc driver to create the linked server?
Rick James says
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.