How to Connect to Oracle database from SQL Server Management Studio

This post shows you How to connect to Oracle database from Microsoft 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.

How to connect to oracle database from sql server management studio

Waiting for the setup screen to show.

How to connect to oracle database from sql server management studio

Selecting language, then click Next

How to connect to oracle database from sql server management studio

Selecting Use Windows built in Account, then click Next

How to connect to oracle database from sql server management studio

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

How to connect to oracle database from sql server management studio

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

How to connect to oracle database from sql server management studio

Clicking Next button

How to connect to oracle database from sql server management studio

Clicking Next button

How to connect to oracle database from sql server management studio

Clicking Install button

How to connect to oracle database from sql server management studio

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

create linked server

Entering name your Linker server

How to connect to oracle database from sql server management studio

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

How to connect to oracle database from sql server management studio

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: [email protected])

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.

How to connect to oracle database from sql server management studio

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.

Related Posts