Failed to connect Microsoft Power BI Desktop to Oracle Database

[GVenkatesh]

Hi

I am trying to connect to Oracle database via PowerBI desktop.

I have configured Oracle connection details in TNSNAMES.ora file but still I am getting the below error

Request you to check and give the right steps to solve this problem

[Ritaf1983]

Hi @GVenkatesh 

To connect to an Oracle database via Power BI Desktop, here are the steps you should follow, as well as troubleshooting tips for common issues like the one you're encountering:

### Steps to Connect to Oracle Database in Power BI Desktop:

1. **Install Oracle Client**:
- Ensure you have installed the correct version of the **Oracle Data Access Components (ODAC)** or the **Oracle Instant Client**.
- **For 64-bit Power BI Desktop**, you need the **64-bit version** of the Oracle Client.
- **For 32-bit Power BI Desktop**, you need the **32-bit version** of the Oracle Client.
- Download and install the [Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client.html).

2. **Configure TNSNAMES.ora**:
- You should have the correct Oracle **TNSNAMES.ora** file, which defines the connection details for your Oracle database.
- Verify that the **TNSNAMES.ora** file is located in the correct directory, typically in the following paths:
- Windows: `C:\Oracle\product\instantclient\NETWORK\ADMIN`
- Ensure that the TNSNAMES.ora file contains the correct **hostname**, **port**, and **service name** for your Oracle database connection.

3. **Add the Oracle Client Path to System Environment Variables**:
- Ensure that the **Oracle client directory** (where the Instant Client is installed) is added to the system's environment **PATH**.
- Go to **Control Panel** > **System and Security** > **System** > **Advanced system settings** > **Environment Variables**.
- Add the path to the `PATH` variable, like this:
- `C:\Oracle\product\instantclient` (or wherever your Oracle Instant Client is installed).

4. **Test the Oracle Connection**:
- Use **Oracle SQL*Plus** or **SQL Developer** to test the connection to the Oracle database outside of Power BI to ensure that the Oracle client is correctly configured.
- Use the same **TNS entry** to test if the connection works using Oracle tools.

5. **Connect to Oracle from Power BI Desktop**:
- Open **Power BI Desktop**.
- Click on **Get Data** > **Oracle Database**.
- In the **Server** field, enter the name of the Oracle instance as specified in your **TNSNAMES.ora** file.
- If you are using a service name, enter it as `host:port/service_name`.
- If you are using a SID, enter it as `host:port/sid`.

### Troubleshooting Common Issues:

1. **Error: "Oracle Client Not Properly Installed"**:
- Ensure that the **bit version** of the Oracle Client matches the **bit version** of Power BI Desktop. For example, if you’re using the 64-bit version of Power BI Desktop, you need the 64-bit Oracle client.
- Ensure the Oracle client path is correctly added to the **Environment Variables** (`PATH`).

2. **Error: "ORA-12154: TNS:could not resolve the connect identifier specified"**:
- Check if the **TNSNAMES.ora** file is correctly configured.
- Ensure that the **TNS entry** is correctly named and matches what is entered in Power BI Desktop.
- Verify that the **TNSNAMES.ora** file is located in the proper directory and accessible by the Oracle client.

3. **Error: "Could not connect to the server"**:
- Ensure that the Oracle database server is running and accessible over the network.
- Double-check the **hostname**, **port**, and **service name/SID** in the **TNSNAMES.ora** file.

4. **Firewall Issues**:
- Ensure that your network firewall is not blocking the connection to the Oracle database. Check with your network administrator if needed.

5. **Check Oracle Home**:
- Make sure that `ORACLE_HOME` is set correctly, especially if you have multiple Oracle clients installed.

6. **Power BI Version**:
- Ensure that your Power BI Desktop is updated to the latest version, as older versions may not support newer Oracle features or configurations.

If my answer was helpful please give me a Kudos and accept as a Solution.

[rajendraongole1]

Hi @GVenkatesh - Ensure that the version of Oracle Client installed on your system is compatible with Power BI Desktop. Power BI requires a 64-bit Oracle Client if you are using the 64-bit version of Power BI Desktop (which is the recommended version).

Install Oracle Client (64-bit): If you haven't already installed the Oracle client, follow these steps:

Download the Oracle Client from the Oracle website.

Oracle Instant Client Downloads
Install the 64-bit Oracle Instant Client corresponding to the database version you are using.
Make sure to select the Basic and ODBC package as needed for Power BI.
Set Up TNSNAMES.ORA File: Ensure that the TNSNAMES.ORA file is configured correctly with your Oracle database connection details. The TNSNAMES.ORA file should be located in one of the following directories:

ORACLE_HOME/network/admin
OR, the location specified in the environment variable TNS_ADMIN

Connect to an Oracle database with Power BI Desktop - Power BI | Microsoft Learn

Power BI: How to Connect to Oracle Database (Part 1) (youtube.com)

Hope the above information helps.