How to Connect to Oracle database from SQL Server Management Studio

If you want to connect to the Oracle database to quick view of the data by using Microsoft SQL Server Management Studio you can create a Linked Server.

Here are the steps to help you setup a linked server.

First off all, you need to download and install the Oracle Data Access Components (ODAC) : Link

After you finish downloading ODAC, you need to extract file and run setup.exe.

Waiting for the setup screen to show.

Selecting language, then click Next

Selecting Use Windows built in Account, then click Next

Specifing an oracle base path to place all oracle software and configuration files.

You should select 
- Oracle Data Provider for .NET
- Oracle Providers for ASP.NET
- Oracle Services for Microsoft Transaction Server
- Oracle Data Access Components Documentation for Visual Studio

Clicking Next button

Clicking Next button

Clicking Install button

Clicking Finish button

After you finish installing Oracle Data Access Components (ODAC), you need to login to your SQL Server by using Microsoft SQL Server Management Studio.

Right-clicking on "OraOLEDB.Oracle" under Server Objects, then select Linked Servers =>Providers =>Properties =>Check to Enable "Allow inprocess".

Next, Right-click on Linked Server, then select New Linked Server...

Entering name your Linker server

- Linked server: <any linked server name> (ex: ORACLE)
- Server type: select Other data source
          + Provider: Oracle Provider for OLE DB
          + Product name: <any> (ex: OraOLEDB.Oracle)
          + Data source: //Server-IP:port/SID (ex: //192.168.0.162:1521/FOXLEARN)

Finally, Select Security, then select option: "Be made using this security context"
- Type remote login username and password for oracle
- Remote login: <username> (ex: foxlearn)
- Password: <password> (ex: 123)

Better way you should create a tnsnames.ora, then copy to the network\admin directory.

ORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = yourdbname)
    )
  )

Next, Open command line, then enter sqlplus userview/foxlearn@123

Clicking OK button to connect to Oracle, If you got an error when testing connection. You should check your server, username and password make sure it's correctly.

Selecting your oracle data from SSMS

SELECT * FROM [LINKED SERVER NAME]..[SCHEMA].[TABLENAME]
EX: SELECT * FROM ORACLE..[FOXLEARN].[Posts]

Through this post, i hope so you can easily connect to oracle database from Microsoft SQL Server Management Studio.