1

My IP address changes frequently and hence I use ssh tunnel via bastion host to connect to Oracle SQL DB. I used JDBC to connect to the Oracle SQL and it works successfully. However, I want to connect to Oracle DB from my QGIS and I tried adding a new connection using Layer -> Vector Layer -> selected Database radio button. When I tested my connection I got the below error.

Are the procedures for connecting QGIS to Oracle SQL directly and for connecting QGIS to Oracle via tunneling using JDBC is the same?

enter image description here

I set up tunnel in SQL Developer as below: enter image description here

And in the tns_names file I added port forwarding as follows:

(prdatp_tp = (description= (address=(protocol=tcps)(port=1522)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=gact2d7ku0cfecu_prdatp_tp.atp.oraclecloud.com))(security=(ssl_server_cert_dn= "CN=adb.ap-tokyo-1.oraclecloud.com,OU=Oracle ADB TOKYO,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) ))

In the sqlnet.ora I provided the path to my wallet folder: WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:/Users/Swathi V/Wallet_prdatp_and_devatp"))) SSL_SERVER_DN_MATCH=yes

I am able to successfully connect to the SQL Oracle DB via bastion host using the above-explained configuration. Coming to QGIS the following is the screenshot of a new connection I tried to create, for which I got an error: enter image description here

Improve this question
3
  • We need details of how you set up your tunnel and what exactly you put in the QGIS connection box Commented Jul 16, 2021 at 11:30
  • @IanTurton I have edited my question to include the complete details of the connection. Commented Jul 17, 2021 at 5:33
  • That is not the correct way to access an Oracle DB (or use a tunnel) - see docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/… but basically your host should be ssh_prdpt and the database should be the actual name of the database (and JDBC is completly wrong) Commented Jul 17, 2021 at 10:04

2 Answers 2

Reset to default
2

Please find in the image attached how I have defined in QGIS the connection to an Oracle Autonomous Database. Please note:

  • You need to download and extract the wallet into the folder defined in the parameter "wallet_location".
  • The parameters "https_proxy" and "https_proxy_port" you need to set if you want to connect via VPN.
  • From what I have observed, the checkboxes underneath the "Authentication" dialog don't seem to work properly. But I might be wrong.

Regards, Karin

QGIS connection to an Autonomous DB

Improve this answer
0

Finally this is what worked for me.

These are the details I entered in the new Oracle connection dialog:

Name: Any name you like to give for your connection

Database: ssh_prdatp_tp_1523

Please note that this is the name of the connection defined in my 'tns_names' file.

Port: 1521

In my 'tns_names' file, I have the below connection defined: ssh_prdatp_tp_1523 =(description= (address=(protocol=tcps)(port=1523)(host=localhost))(connect_data=(service_name=gact2d7ku0cfecu_prdatp_tp.atp.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-tokyo-1.oraclecloud.com,OU=Oracle ADB TOKYO,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

For the authentication credentials, I just used "Basic" option as shown in the screenshot below:enter image description here

Thanks to everyone who tried to help me out and I hope this helps others too.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.