Oracle19c - Issue using db-link to GCP with Simba Driver

[Simone88]

Dear All,

In CASE 2, simba driver tries to find remote table in US location and not in UE location where it is.
Same problem using version 3.0.0 of Simba driver.
All Simba drivers versions work if remote table is on GCP dataset with location US.

CASE1: Simba Driver Version 2.1.11
Driver Simba: configuration file: odbc.ini e odbcinst.ini 
Oracle Database: configuration file: tnsnames.ora, listener.ora,initbq.oraand creation of oracle db-link "BQ".
GCP: Catalog on GCP project with EU location.
TEST: Connect with sqlplus and query run on GCP remote table ( project with UE location ).

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 5 11:39:52 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:Last Successful login time: Tue Sep 05 2023 11:38:45 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> select "contract_id" from "profiling_datamodel"."contracts"@BQ;

contract_id
-----------
          3
          1
          5
          2
          4

CASE2: Simba Driver Version 2.5.2

Driver Simba: configuration file: odbc.ini e odbcinst.ini 
Oracle Database: configuration file: tnsnames.ora, listener.ora,initbq.oraand creation of oracle db-link "BQ".
GCP: Catalog on GCP project with EU location.
TEST: Connect with sqlplus and query run on GCP remote table ( project with UE location ).

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 5 10:39:25 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password: Last Successful login time: Tue Sep 05 2023 10:39:46 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> select count(*) from "profiling_datamodel"."contracts"@BQ

select count(*) from "profiling_datamodel"."contracts"@BQ

*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Simba][BigQuery] (100) Error interacting with REST API: Access Denied: Table
ita-da-dev:profiling_datamodel.contracts: User does not have permission to query table ita-da-dev:profiling_datamodel.contracts, or perhaps it does not exist in location US. {42000,NativeErr = 100}
ORA-02063: preceding 2 lines from BQ

Someone have some idea on how I can solve this issue?

Best Regards,

Simone

[ms4446]

It seems that the Simba Driver version 2.5.2 is trying to locate the remote table in the US location, while the table is actually in the EU location. This is causing the "Access Denied" error.

There are a few possible solutions to this issue:

• Specify the Location in the Connection String: Some drivers allow specifying the dataset's location directly in the connection string or DSN configuration. Check the Simba driver's documentation to see if there's a way to specify the location.

• Configure the Simba Driver: Ensure that the odbc.ini and other configuration files for the Simba driver don't have any hard-coded location values set to US. If there's a parameter to specify the location, set it to EU.

• Upgrade Consideration: Although you mentioned trying Simba Driver version 3.0.0, it might be worth checking if there's an even more recent version available that addresses this specific issue.

Once you've tried one of these solutions, please attempt running the query again. If you continue to encounter the "Access Denied" error, please provide additional details about your environment, such as the operating system and Oracle Database version, for further assistance.

[Simone88]

thanks for your feedback.

We tried your proposed solution, but they didn't work. About Specify the Location in the Connection String what do you mean exactly? do you have an example? About the connection to GCP, we use a json file.

About our environment we have Oracle 19.19 as Database, Oracle 19.3 as Oracle Client and Red Hat Enterprise Linux Server release 7.9

Thanks and Best Regards

Simone

[ms4446]

I apologize for the confusion. To specify the location of the BigQuery dataset in the connection string, you might use a format similar to the following (please verify with official documentation):

Driver=Simba BigQuery;
Server=https://bigquery.googleapis.com;
ProjectId=my-project;
Dataset=profiling_datamodel;
Location=EU;
      

Connection to GCP using a JSON File

If you're using a JSON file for authentication with GCP, you can specify the path to the JSON file in the connection string using the KeyFile parameter:

Driver=Simba BigQuery;
Server=https://bigquery.googleapis.com;
KeyFile=/path/to/keyfile.json;

Environment

Ensure that Oracle 19.19, Oracle Client 19.3, and Red Hat Enterprise Linux Server release 7.9 are compatible with the version of the Simba BigQuery ODBC driver you're using. Compatibility details should be available in the driver's official documentation.

Troubleshooting

If you're still facing issues connecting to BigQuery:

• Verify that the BigQuery table exists in the specified location.
• Ensure the user account has the necessary permissions to access the BigQuery table.
• Check the Simba BigQuery ODBC driver logs for any errors or clues.
• Consider trying a different version of the Simba BigQuery ODBC driver.
• Reach out to Simba support for further assistance.

[Simone88]

Hi ms4446,

I checked the simba documentation and no Location parameter is mentioned. Furthermore, I check also the odbc.ini and it seems that is correct. So I think that I have to reach out to Simba support for further assistance, right? Thanks in advance. Simone

[ms4446 · Accepted solution]

Yes, if you've already verified the Simba documentation and checked the odbc.ini configuration, and everything seems correct, then reaching out to Simba support is the next logical step. They might be aware of any nuances or specific issues related to the combination of Oracle, the Simba driver, and BigQuery, especially when it comes to dataset locations.