Setting Up an Oracle Connection From Microsoft SQL Studio

Context

This document assumes that Oracle client software has already been installed.

Solution

Add an entry for dwprod in the oracle tnsnames.ora file. This file is commonly found in a directory similar to: c:\oracle/product/11.2.0/client_1/network/admin

dwprod =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dwprod.mit.edu)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DWRHS)
    )
  )

ODBC Configuration

1. Open ODBC Data Source Administrator click on System DSN and click on the Add… button.
   
   
   
2. Select The Oracle driver (may vary depending on Oracle client version) and click Finish.
   !
   
   
3. Enter dwprod in the first three boxes. All other settings can be left at their default values and click OK
   
   
   
4. Open Microsoft Sql Server Management Studio. On the menu on the left expand “Server Objects” and right click on Linked Servers. Select New Linked Server… from the popup menu.
   
   
   
5. On the “General” page enter the following:
   • Linked server: DWPROD
   • Select the “Other data source” radio button
   • Provider drop down select ‘Oracle Provider for OLE DB’
   • Product Name: OraOLEDB.Oracle
   • Data source: DWPROD
     
     
     
6. Go to the ‘Security’ page and select the Be made using this security context radio button. Enter the Oracle username you will be connecting with for Remote login text box and the password for that user in the With password text box.
   
   
   
7. Click “Ok”
   
   

Test Your Connection

To test, open a query window and execute the following:

select * from dwprod..SYS.DUAL

(!_) While Oracle is not case sensitive for table names sql server is so you must reference all Oracle usernames and objects in uppercase or you will get an error similar to the following:

Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "OraOLEDB.Oracle" for linked server "dwprod" does not contain the table ""sys"."DUAL"". The table either does not exist or the current user does not have permissions on that table.

See Also

• Oracle Landing Page