User Credential Issue about gateway connection to ORACLE ADW

[ddchoi]

I'm testing the connection between ADW and Power BI Service.

Following the guide, I successfully completed the gateway configuration. During the ADW user authentication step in the "Connect Datasource" process, the connection works fine with the ADMIN user. However, when using a general user, an error occurs. The general user has been granted CONNECT and SELECT privileges. Are there any additional privileges required?

The general user can connect to ADW using Power BI Desktop with only the CONNECT and SELECT privileges, but authentication fails when using the gateway In Power BI Service.

If you happen to have any relevant information regarding this issue, I would greatly appreciate it if you could share it.

[Anonymous]

Hi @ddchoi ,

Whether the advice given by rajendraongole1  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[rajendraongole1 · Accepted solution]

Hi @ddchoi - Oracle Autonomous Data Warehouse (ADW) authentication is handled when connecting through Power BI Desktop versus Power BI Service with a gateway.

1. Missing Privileges for General Users
The CONNECT and SELECT privileges are typically sufficient for querying data in Power BI Desktop. However, when using Power BI Service through a gateway, the general user might require additional privileges due to how the gateway accesses the data.

Additional Privileges to Grant:

GRANT CREATE SESSION TO general_user;
GRANT SELECT ON ALL TABLES TO general_user;
CREATE SESSION: Ensures the user can establish a session with the database.
SELECT ON ALL TABLES: If the user’s privileges are limited to specific schemas, ensure they have SELECT privileges on all required tables.
2. Verify Gateway Configuration
Ensure that the gateway is properly configured to use the credentials of the general user.
In the Manage Gateways section of the Power BI Service, verify the data source settings for:
Authentication Method: Ensure it matches the general user credentials.
Connection Test: Check if the test succeeds with the general user credentials.
3. Validate Oracle Wallet Configuration
Power BI Service often uses Oracle Wallet for secure authentication. Confirm the following:

The Oracle Wallet files (cwallet.sso, sqlnet.ora, tnsnames.ora, etc.) are correctly configured on the gateway machine.
The general user is allowed access via the Oracle Wallet configuration.

6. Connection Differences Between Desktop and Service
Power BI Desktop connects directly to the Oracle database, while Power BI Service uses the gateway to mediate connections. This introduces subtle differences:

Ensure that the general user account is valid and not locked when used via the gateway.
Check if the network configuration for the gateway allows access to ADW.
7. Debugging Steps
Enable Logging: In the Oracle gateway settings, enable detailed logging to capture errors.
Check Error Details: Look for more specific error messages in the Power BI Service or gateway logs.
Test Connection with SQL Developer: Use the general user credentials to connect to ADW via SQL Developer to ensure they work independently.