Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago

ODBC Gateway Connection Error

Hello

I'm trying to set up a gateway in the power bi services to an ORACLE database, I'm doing it using ODBC.

I have the following steps done and validated.

  1. I installed the gateway on the machine.
  2. I have installed the oracle client.
  3. I have configured in tsnames.ora with the data from the database.
  4. I have configured the DSN which retrieves the database data previously configured in the tsnames, the connection is successful.
  5. I have connected from power bi on the machine by ODBC to the database and can connect and extract data without problems.
  6. When parameterizing the gateway in the power bi services I get the following error:

You may need to consider some security-related settings on the machine?

I appreciate your help.

Thank you

1 Reply

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi Syndicate_Admin 

    The issue occurs when setting up a gateway in Power BI Service to connect to an Oracle database via ODBC. The error indicates that the TNS name cannot be resolved (ORA-12154).

    Solution:
    To resolve this, ensure the gateway type is correctly configured, verify the tnsnames.ora and credentials setup, and align the configurations across Power BI Desktop, the gateway, and Power BI Service. Follow these steps:

    1. Set the Correct Gateway Type
    Install the Standard Mode Gateway, not the Personal Mode Gateway.
    Standard Mode is required for shared connections and to allow Power BI Service to access the gateway.
    2. Verify Configuration in Desktop and Service
    In Power BI Desktop:

    Confirm the ODBC connection uses valid Oracle credentials (username/password).
    Refresh the dataset to ensure the connection works correctly locally.
    In Power BI Service:

    Go to the dataset settings (Settings > Datasets).
    Under Gateway Connection, select the configured gateway.
    Enter the credentials in Data Source Credentials (Oracle username/password).
    Use the Test Connection button to validate the configuration.
    3. Check tnsnames.ora and Environment Variables
    Ensure the tnsnames.ora file is correctly configured and error-free.
    Verify the TNS_ADMIN environment variable points to the folder containing the tnsnames.ora file.
    Restart the machine after setting TNS_ADMIN.
    4. Test the Connection
    Use the tnsping command to test connectivity to the Oracle database:
    bash
    Copy code
    tnsping <TNS_ALIAS>

     

    +
    Try follow the linked video guide (3 videos):
    https://www.youtube.com/@fishleaf/search?query=oracle

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.