How to Connect to Oracle database from SQL Server Management Studio

How to Connect to Oracle database from SQL Server Management Studio

To connect to an Oracle database from SQL Server Management Studio (SSMS), you'll need to use a linked server.

You need to have the Oracle Client installed on the server running SQL Server, or you can use the Oracle Data Provider for .NET (ODP.NET). Alternatively, you can also use Microsoft OLE DB Provider for Oracle.

Download and install the Oracle Data Access Components (ODAC) or ensure you have the Oracle Instant Client installed on the machine where SQL Server Management Studio is running.

You can download ODAC from the Oracle website, then install the Oracle Data Access Components (ODAC) : Link

How to install Oracle ODAC driver?

After you finish downloading ODAC, you need to extract file and run setup.exe, then install Oracle Client software on the SQL Server machine. This is required for establishing a connection to Oracle databases.

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

Linked Server Oracle Sql Server

After you finish installing Oracle Data Access Components (ODAC), You need to Open SQL Server Management Studio, then connect to your SQL Server instance, and navigate to Server Objects -> Linked Servers.

Next, Right-click on Linked Servers and select "New Linked Server".

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

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.

Once the linked server is created, you can test the connection by expanding the Linked Servers node in SSMS, right-clicking on the newly created linked server, and selecting "Test Connection". Ensure that the test is successful.

You can now access Oracle tables from SQL Server Management Studio using four-part naming convention

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

Remember to replace [LinkedServerName], [OracleSchema], and [OracleTableName] with the appropriate values for your Oracle setup.

By following these steps, i hope so you can easily connect to an Oracle database from SQL Server Management Studio.