Creating a Linked Server for Oracle in 64bit SQL Server

This post is a walkthrough of creating a Linked Server to Oracle from 64bit SQL Server.  There's lots of information on doing this on the web, but much of it is out-of-date.

First, install the correct Oracle drivers.  You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment.  They are available here:

64-bit Oracle Data Access Components (ODAC) Downloads

https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path: c:\oracle\odac64 and c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server.

First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 

Then create the linked server definition.  Instead of a TNSNames alias, use an EZConnect identifier.  Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

 exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''

 exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

 exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='xxxxxx'     

Now you're ready to test.  We configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

 exec ('select 1 a from dual') at MyOracle

That's it.

• Anonymous
  October 30, 2013
  I have a Windows 2012 R2 with SQL Server 2012 R2 installed on it.  I followed the instruction above.  And test the connection and I get this error. TITLE: Microsoft SQL Server Management Studio

• Anonymous
  October 30, 2013
  I checked the registry and none of the files listed in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI are in the folder indicated.

• Anonymous
  December 19, 2013
  BEWARE: tested on several Systems, just doesnt work somethings missing in this HowTo - it cannot create an instance Error 7302

• Anonymous
  January 08, 2014
  Note that the SQL Server instance must be 64bit too.  A 32bit SQL Server would need the 32bit OleDB provider.

• Anonymous
  January 10, 2014
  Very Nice Explanation Thanks

• Anonymous
  January 30, 2014
  The comment has been removed

• Anonymous
  February 13, 2014
  Thanks for the post! I was using a Windows 7 64 bit system and connecting to an Oracle 11g on CentOS on a VM in the same system. When using this guidelines for 64bit, it did not work. However, the same steps used with 32 bit ODAC and Oraclie Client 11g worked like a charm! Thanks a lot! Regards Dev

• Anonymous
  March 10, 2014
  The comment has been removed

• Anonymous
  April 13, 2014
  you must copy your tnsnames.ora file to c:oracleodac64network

• Anonymous
  April 27, 2014
  After two days of struggle with creating linked server on Windows Server 2012 R2 and Sql server 2012 - found solution from Troy 7's comment (Thanks Troy) The Data Source I was providing was the SID as defined in the tnsnames - which worked fine on Windows Server 2008/Sql Server 2012 linked server In Windows server 2012 - it's expecting Data Source:  //IPAddressOrHostnameOfOracleServer:PORT/SERVICE_NAME In both cases I was creating the linked server to the same Oracle db on the same server. No idea why the detailed definition was needed ...

• Anonymous
  April 27, 2014
  Thanks to Okan Tekeli too... I had to copy the tnsnams.ora into the c:oracleodac as well. I had it in the Oracle client networkadmin. This was fine for Windows Server 2008 Windows server 2012 R2 expects a copy of the tnsnames.ora in c:oracleodac

• Anonymous
  June 09, 2014
  Thanks Dave, this worked great!

• Anonymous
  July 28, 2014
  Thanks for a great writeup! For those who have not been able to make this work, you can try to doublecheck if you have launched CMD as admin before running the install.bat command. Without this you might still get entries in the registry and files in the right places (if you accept regedit prompts during install) - but the provider will not be registrered with SQL

• Anonymous
  August 06, 2014
  Can someone please explain me the second parameter "Oracle" in the "sp_addlinkedserver " query? I am little bit confuse over it.

• Anonymous
  August 19, 2014
  Great topic. Straightforward. To the pont. You don't have to install Oracle Client. Only Oracle Driver will do. Everything works for first time.  I wish only such instruction to be on internet. Thank You  very much.

• Anonymous
  September 23, 2014
  SQL Server 2012 R2 on Windows Server 2012 R2 Datacenter Followed your instructions and installed from ODAC121012Xcopy_x64.zip.  When expanding all the way down to Tables in newly created linked server, error 'The OLE DB provider "ORAOLEDB.Oracle" has not been registered.(Microsoft SQL Server, Error:7403)' occurred. On examination,the new folders and installed files properly show up in Windows Explorer, but data provider ORAOLEDB.Oracle is not in the Provider folder under Linked Servers in SSMS. Checked and rebooted again and again, but still the same.  What could be wrong?  Your help would be greatly appreciated - Like you said, there were lots of postings on this, but most are out-of-date. Thanks in advance! David

• Anonymous
  October 27, 2014
  Best set of instructions for this. thanks.

• Anonymous
  November 09, 2014
  Thanks for the help, it works.

• Anonymous
  November 17, 2014
  the Error with 7403 can solved. You must aktivate the checkbox for "in Process allowed". in the linked server can you config the driver. there you must open OraOleDB.Oracle.

• Anonymous
  November 19, 2014
  OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-03134: Connections to this server version are no longer supported.". Trying to set up a linked server from 2014 sql express to a server running oracle 9.2.0.8.0

• Anonymous
  December 15, 2014
  i doing follow instruction and complete all but when i tried to test connection my sql server auto stop service. Could you please help me to solve this problem?

• Anonymous
  January 10, 2015
  The comment has been removed

• Anonymous
  January 10, 2015
  @daniesh. Check the NTFS permissions of the SQL Server service account on the Oracle client folders.

• Anonymous
  February 02, 2015
  thanks!  been trying to get this working all day.  your post did the trick.

• Anonymous
  March 20, 2015

1. This procedure does not register ORAOLEDB.Oracle provider with SQL Server.
2. The solution below failed under the standard user command prompt for me. I had to do it as an admin.
3. The fourth parameter (install_dependents) is true by default. So, what worked for me is this: Admin Command Prompt>instal all c:OracleODAC odac64 This was tested with MS SQL Server 2008 R2 SP2 and ODAC 12c R3 (12.1.0.2.1).

• Anonymous
  May 15, 2015
  Finally a simple guide, all steps worked fine. I got ORA-12514 becasue I used a wrong oracle service name. these commands (from oracle server) could be useful to get info: lsnrctl status lsnrctl service ciao

• Anonymous
  May 29, 2015
  The comment has been removed

• Anonymous
  June 15, 2015
  Very detail to the point. First class

• Anonymous
  June 20, 2015
  Thanks exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 exec sp_addlinkedserver N'OrawsvtestLinkedSVR', 'Oracle', 'ORAOLEDB.Oracle', N'OraTnsEntryName', N'FetchSize=8000', '' exec master.dbo.sp_serveroption @server=N' OrawsvtestLinkedSVR ', @optname=N'rpc out', @optvalue=N'true' exec sp_addlinkedsrvlogin @rmtsrvname=' OrawsvtestLinkedSVR', @useself=N'FALSE', @rmtuser=N'Oraid', @rmtpassword='Pwd'    

• Anonymous
  June 30, 2015
  Windows Server 2012 x64, SQL Server Ent x64. Completely removed any sign of oracle from the server, then downloaded the ODAC112040Xcopy_64bit.zip (as the remote oracle DB is v11.2.0.4.0) created a linked server with your instructions (but I used TNS instead of ezconnect) and everything works like a charm. Thanks a lot

• Anonymous
  June 30, 2015
  Sorry my SQL version is 2012 as well (11.0 SP1)

• Anonymous
  July 14, 2015
  The comment has been removed

• Anonymous
  August 17, 2015
  when i want to linked server test connection, I receive below error: 32-bit OLE DB provider "ORAOLEDB.Oracle" cannot be loaded in-process on 64-bit SQL Server (7438)

• Anonymous
  August 30, 2015
  It worked only after put a path(variables) with TNS_ADMIN = path of where tnsnames are

• Anonymous
  September 04, 2015
  Great great topic. Things are so easy, if you know how... It works fine for me on MSSQL Server 2014 SP1.

• Anonymous
  September 30, 2015
  I agree with Radek together with www.orafaq.com/.../EZCONNECT I was able to solve my problem

• Anonymous
  October 12, 2015
  Hello, This worked for me on MSSQL 2014, and I've tried before on 2008, so the problem I was getting was the Oracle driver is missing, but in 2014 it's the driver is available. Botan from Somalia

• Anonymous
  October 12, 2015
  The comment has been removed

• Anonymous
  October 14, 2015
  Installing SqlSERVER 2008 R2 on Windows Server 2012. The ORAoleDB.oracle providers would not show up under providers in SSMS. This was solved by registering the oraoledb12.dll  by running the cmd.exe as an admin(otherwise error 0080070005).  Command: regsvr32.exe c:oracleodac64binOraOLEdb12.dll.

• Anonymous
  November 24, 2015
  Thank you so much, it's work for me on sql server 2014 64bit

• Anonymous
  March 04, 2016
  Works great, thanks.

• Anonymous
  March 09, 2016
  The comment has been removed

  • Anonymous
    April 04, 2016
    Looks like an update to the blog engine broke it. I reformatted the snippets. Thanks.

• Anonymous
  March 09, 2016
  This also worked for me. Thank you.One thing to note, the EZCONNECT string seems to only work with the Service Name of the database, not the SID.E.g. //server-name.domain:1521/service_name

  • Anonymous
    June 07, 2016
    Fyi, about the "cannot create an instance of ole db provider oraoledb.oracle for linked server msg 7302", this was happening in our new production server but not in our development server. Development server that worked did NOT have "in process" selected, so Oracle was safely running fine outside of SQL Server's process. However, strangely, only way to get it to work in new production server was to select "in process", but this is risky. Anyways, after about a hundred different security configuration changes, MSDAINITILIZE stuff, etc., was about to re-image with Windows Server 2012 R2 again when we decided to run Windows Updates (yeah I know...). Anyways, that worked! Can't really say why, but after installing latest windows updates running Oracle out of process (de-selecting "in process) in our new production server works now! Thought that might help someone out there...

• Anonymous
  May 05, 2016
  If you get the following error because your are running a distributed transaction:ole db provider for linked server was unable to begin a distributed transactionrun the following command to install the OracleMTS.\install.bat oramts c:\oracle\odac64 odac64 falserun this after the other install command.