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

http://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.

 

 

 

 


Comments (46)

  1. Sam Ware says:

    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

    ——————————

    The test connection to the linked server failed.

    ——————————

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    The OLE DB provider "ORAOLEDB.Oracle" has not been registered. (Microsoft SQL Server, Error: 7403)

    For help, click: go.microsoft.com/fwlink

    ——————————

    BUTTONS:

    OK

    ——————————

  2. Sam D Ware says:

    I checked the registry and none of the files listed in

    HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI

    are in the folder indicated.

  3. Dave Derb says:

    BEWARE: tested on several Systems, just doesnt work

    somethings missing in this HowTo – it cannot create an instance Error 7302

  4. Note that the SQL Server instance must be 64bit too.  A 32bit SQL Server would need the 32bit OleDB provider.

  5. Rohit Gopidi says:

    Very Nice Explanation

    Thanks

  6. Troy7 says:

    Thank you very much for this post.  This worked very well for me and I was able to skip several steps.  I did not need to download and register the path of the ODAC components having already installed the Oracle tools for Visual Studio.  Check to see if the OraOLEDB.Oracle provider is already available in SQL Server Management Studio (expand 'Server Objects' – 'Linked Servers' – 'Providers').  If it is, you may also be able to skip the first part.

    I then executed the first two SP's:

    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 used the GUI to enter the following information:

    Linked Server:  FriendlyNameOfTheLinkedServer

    Server Type: Select "Other data source"

    Provider: Oracle Provider for OLE DB

    Product Name: Oracle

    Data Source:  //IPAddressOrHostnameOfOracleServer:PORT/SERVICE_NAME

    ***Note*** the Data Source information can all be pulled from your TNS.ora file or parameters used in an EZConnect profile

    Last step was specifying a login.  For my particular setup, I selected the last option on the Security page/tab:

    "Be made using this security context:"

    Remote login:  USERNAME

    With Password:  PASSWORD

    As the author noted, be sure that you are using the correct driver for your version of Oracle and SQL.

  7. Dev says:

    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

  8. ORAHeadbanger says:

    Dude I got my sandbox server installed perfectly without using this method and all documented

    Been battling for a long LONG time on the DEV box and been looking past this all the time

    GIVE THAT MAN A BELLS

  9. Okan Tekeli says:

    you must copy your tnsnames.ora file to c:oracleodac64network

  10. K Kumar says:

    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 …

  11. K_Kumar says:

    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

  12. SQLMonkey says:

    Thanks Dave, this worked great!

  13. Henrik Bernhard says:

    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

  14. Jitender-Muwal says:

    Can someone please explain me the second parameter "Oracle" in the "sp_addlinkedserver " query? I am little bit confuse over it.

  15. Krzysiek says:

    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.

  16. David says:

    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

  17. surendra says:

    Best set of instructions for this.

    thanks.

  18. Jay says:

    Thanks for the help, it works.

  19. Sven says:

    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.

  20. Erik says:

    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

  21. mac says:

    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?

  22. daniesh says:

    Hi All,

    When i am trying to create a linked using windows authentication, Linked server is getting created successfully. When trying to create using sql server authentication mode, getting an error.

    cannot create an instance of ole db provider oraoledb.oracle for linked server "XXX" msg 7302

    I tried lots of things still the same, Enabled to allow in process. Oracle client version is 11.2.0.4 and Oracle DB version is 11.1.0.7 and ms sql server is 2008.

    Can anybody please help.

    Thanks

  23. @daniesh. Check the NTFS permissions of the SQL Server service account on the Oracle client folders.

  24. dale says:

    thanks!  been trying to get this working all day.  your post did the trick.

  25. DK says:

    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).

  26. byman64 says:

    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

  27. Rishi says:

    How to use the tnsnames.ora could someone clarify where the tnsnames.ora files needs to be copied for this to work successfully, tried n number of combinations but all error out in ORA-12154

    Ta

  28. Raj says:

    Very detail to the point. First class

  29. Jayant Dass says:

    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'    

  30. Lance R. says:

    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

  31. Lance R. says:

    Sorry my SQL version is 2012 as well (11.0 SP1)

  32. Installed but no driver can be loaded says:

    Hello,

    I've installed the ODAC121021Xcopy_x64.zip driver from Oracle with the following command run with administrative privileges:

    > install oledb c:oracleodac64 odac64 true

    I can see the files (I don''t know if they're all the files.. e.g. the oledb folder contains only a readme.html file) in the correct directories.

    I've changed the path according to this post's instructions.

    On a Win 8.1 box everithing worked well and, after reboot, I was able to see a driver and connect to my Oracle server.

    Same operations on a Win Server 2008 R2 (64 bit), but no Oracle driver can be found in any context (Drivers in SQL Server Management Studio, Drivers in ODBC Manager in control panel, etc.).

    Any help to troubleshoot this?

    Thanks,

    Andrea

  33. hosham says:

    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)

  34. Simaika says:

    It worked only after put a path(variables) with

    TNS_ADMIN = path of where tnsnames are

  35. Radek (Czech Republic) says:

    Great great topic. Things are so easy, if you know how…

    It works fine for me on MSSQL Server 2014 SP1.

  36. Hans says:

    I agree with Radek together with http://www.orafaq.com/…/EZCONNECT I was able to solve my problem

  37. Abdallah Botan says:

    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

  38. Pavan says:

    Thanks Troy7 .. Your comment helped me.

    My mistake was in this line

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

    Instead of N'//172.16.8.119/xe' give proper  Data Source:  //IPAddressOrHostnameOfOracleServer:PORT/SERVICE_NAME

  39. Brian Foroud says:

    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.

  40. Binh Vu says:

    Thank you so much, it's work for me on sql server 2014 64bit

  41. Bruno Correia says:

    Works great, thanks.

  42. Sean S says:

    Ouch! The blog’s formatting of quotes is horrible.

    I’m guessing the double quote at the end of the exec sp_addlinkedserver line is supposed to be two single quotes, i.e. ”?

    1. Looks like an update to the blog engine broke it. I reformatted the snippets. Thanks.

  43. Sean S says:

    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

    1. Jim L says:

      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…

  44. Robert Sloan says:

    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 transaction

    run the following command to install the OracleMTS

    .\install.bat oramts c:\oracle\odac64 odac64 false

    run this after the other install command.

Skip to main content