Connecting to Oracle from SQL Server

Update: I have posted a revised guide for connecting with SSIS using newer components.

Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you”ll have to install some software on your server to make things work. I”ve put together a short guide on connecting to Oracle from SQL Server. The process doesn”t take very long but there are several hoops to jump through.

  1. Install Oracle Database 10g Client Release 2
    1. Install using the InstantClient option
  2. Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
    1. Select the Oracle Data Access Components option (not .NET!)
  3. Edit TNSNAMES.ora
    1. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
    2. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):
      DMDEV =
       (DESCRIPTION =
       (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
       )
       (CONNECT_DATA =
       (SERVICE_NAME = FRIENDLYNAME)
       )
       )

      There are a couple of things you need to change:

      • HOST = SERVERNAME. The casino online real money value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
      • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
  4. REBOOT!
  5. Configure provider in SQL Server
      OraOLEDB.Oracle Provider Menu Item

      OraOLEDB.Oracle Provider Menu Item

    1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”
    2. Enable "Allow inprocess"

  6. Create a linked server to the Oracle Database
    1. General

      1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
      2. Provider: Oracle Provider for OLE DB
      3. Product Name: “Oracle” is fine here
      4. Data Source: This should match the HOST you defined in TNSNAMES.ora
      5. Create a Linked Server

        Create a Linked Server

    2. Security
      1. Select Be made using this security context and supply the remote login and password
  7. Query the linked server:
    SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP 

44 comments on “Connecting to Oracle from SQL Server

  1. Pingback: Curia Damiano blog | SQL Server: how create a linked server to Oracle

  2. Pingback: Oracle with Kerberos authentication and Windows 2003 Server as KDC - Just just easy answers

  3. Pingback: Vue Oracle dans Entity Framework 5

  4. Edward Polley on said:

    Thanks – a lot of articles didn’t work but yours did. Much appreciated.

  5. Manuel Sarmiento on said:

    extremely useful !!
    Thanks =D.

  6. Hi,
    I had follow the steps and also enable the inprocess. When creating a new link server, below msg is given.

    Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “LINK1″.
    OLE DB provider “OraOLEDB.Oracle” for linked server “LINK1″ returned message “”. (Microsoft SQL Server, Error 7303)

    How do i solve this issue?

    Thank you.

  7. This is great article! thanks

  8. Fabio Mariano on said:

  9. Fabio Mariano on said:

    Please check the link below.

  10. luis on said:

    Thank you. I had trouble connecting first using “instant client” only as the Oracle provider never showed up under SQL’s Providers. I installed Oracle Client and these parameters form worked for me

    Linked server: any name
    Provider: Oracle Provider for OLE DB
    Data source: host:port/service_name all these ones as they are written in the tnsnames.ora file

    This website helped me a lot as well for understanding what I was doing:

    http://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/

  11. Rocky on said:

    I have installed the x32 and x64 Oracle client on my SQL 2008R2 server. I’ve created the linked server using OraOLEDB.Oracle and get the following error when trying to test the connection:

    Cannot Initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “SQLOraTest”.
    OLE DB Provider “OraOLEDB.Oracle” for linked server “SLQOraTest” returned message “ORA-03134: Connections to this server version are no longer supported” Microsoft SQL Server error: 7303)

    I’ve followed the steps above, any ideas as to what the problem is?

    Thanks for your input.

  12. Thank a Lot Taylor.
    This morning i came to work i tried entering into my database through SQL Authentication and i get this error below

    A connection was successfully established with the server, but then an wrror occurred during the login process. (provider: shared memory provider, error:0 – No process is on the other end of the pipe.) (microsoft SQL Server, Error: 233).

    i went to the SQL configuration manager and checked for SQL Server Network Configuration, i can’t find the TCP/IP there to enable it

    How do i solve this issue

  13. Oracle Client must be installed on the same server as the SQL Server database

  14. I have installed sql server 2008 on a win server 2008 server, and I installed the Oracle Client and database on another server.
    my problem is the Provider “OraOLEDB.Oracle” is missing. and I cant make a link with the Oracle server.
    can anyone help thanks

  15. Michael Kaufman on said:

    I am connecting a SQL Server to an Oracle 11g sever. When I try to query data from Oracle through the SQL Server I get error messages:

    Server: Msg 7354, Level 16, State 1, Line 1
    OLE DB provider ‘OraOLEDB.Oracle’ supplied invalid metadata for column ‘DTTM_STAMP_SEC’. The data type is not supported.

    and

    Server: Msg 7318, Level 16, State 1, Line 1
    OLE DB provider ‘MSDASQL’ returned an invalid column definition.

    Which message I get depends upon whether I am connecting through the Oracle Provider for OLE DB or the Microsoft OLE DB Provider for ODBC.

    Does anyone know what I am doing wrong?

  16. dmcmillan on said:

    I have figured out that this has to do with using update with openquery, I just need to figure out the right syntax. Thanks very much.

  17. dmcmillan on said:

    Ok, that’s what I thought. Select is working ok, but I’m having trouble getting an update to work. Am getting the following error, but I’m sure the permission is correct for the user I set:

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “update test.employee set email_address = “dmcmillan@mgh.org”
    where employee = 888888″. The OLE DB provider “OraOLEDB.Oracle” for linked server “lawtest” indicates that either the object has no columns or the current user does not have permissions on that object.

    Thanks very much.

  18. @dmcmillan

    By selecting “Be made using this security context”, you’re requesting that the credentials used are those ones you’re providing in that dialog box.

    If you want the connection to be attempted with the same credentials as you’re logging in, select “Be made using the login’s current security context”, though I find this often doesn’t work, especially with Windows Authentication.

  19. dmcmillan on said:

    I set up a link from SQL Server 2008R2 to an Oracle 10g database. Using “be made using this security contex” entered a login name and password.
    Do you actually connect to the database as that user? Or are you connected as the user you are logged into SSMS as?
    Thatnks very much.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>