4 Ways to Connect to Autonomous Database on a Private Network

April 12, 2023 | 7 minute read
Quentin Jansen
Cloud Solutions Architect
Text Size 100%:

Normally, all production databases are deployed into private subnets within customer networks to limit the access to connections from within the network only. This is very good for security but does make it harder for developers or database administrators to connect to those databases. 

Most people will use a publicly accessible VM, or Jump server, to connect to the database. We'll show you how this is done, but we'll also show how to connect if you do not have access to a VM using different OCI services.

For all the connections it is assumed that because the database is in a private subnet within the network, mTLS has been disabled and the network is configured to allow access to the necessary database ports.

1. Connect from an accessible Jump Server on the same private network

  • Log into a jump server (aka VM) that is on the same private network as your Autonomous Database

  • Copy the connection string from Database connection screen (make sure you select TLS in the TLS authentication dropdown

    Copy connect string
    Copy connect string
  • From a terminal window ssh into your server as opc
  • Switch to the oracle user
sudo su - oracle
  • Initiate SQLcl or sqlplus with the user and password and paste the connection string at the end:
sql <user>/<password>@<connection string>

or

sqlplus <user>/<password>@<connection string>

2. Connect using Cloud Shell

Cloud Shell is a web browser-based terminal accessible from the Oracle Cloud Console. Cloud Shell is free to use and provides access to a Linux shell. What's nice is that it includes useful tools for connecting to and using OCI services, including the OCI APIs, CLI and SQL. Let's see how to connect to Autonomous Database using Cloud Shell.

  • Open Cloud Shell

Open cloud shell

  • Change the Cloud Shell network to point to the private network where your endpoint is located (this will bring up a screen where you enter the network, subnet and network security group that you want to use)

Cloud Shell private network setup

  • Wait for it to connect
  • At the command prompt enter ‘sql’ to start SQLcl (same will work with SQLplus)
  • When it prompts for the Username, enter the username you want to use followed by @ and the connect string for your database
<username>@<connect string>
  • When it prompts for the Password, enter the password for that user

You are now connected to your database!

3. Connect using OCI Connections

Connections is an OCI service that allows you to connect to all your databases in a secure way without having the create and maintain bastion servers or other components. The instructions on how to do this can be found here.

Once you have created the connection, you can access the database from a SQL worksheet or SQLcl session within the Cloud Shell. 

To access the database with the SQL worksheet:

  • Click on SQL worksheet in the console within the connection you have created

Connect with SQL Worksheet

  • Ensure that the connection on the top right of the worksheet is the one you want to query (this drop down allows you to easily switch between querying the various databases for which you have set up connections)
  • You can now enter your query and run it against the database. You can also load scripts from object storage or from a local drive

To access the database using SQLcl:

  • Click Launch SQLcl in the console within the connection you have created:

Launch SQLcl

  • This launches a pop-up page where you select the bastion to use (if there is no bastion available or you want to create a new one, click Create bastion and follow the steps to create one)
  • Click Launch at the bottom
  • This will initialize the Cloud Shell, which in turn creates the tunnel through the bastion and connects to the database

Use cloud shell and SQLcl to query ADB

You can now use the SQLcl session!

4. Manually create a tunnel from SQL Developer on your computer using a bastion host

The information that you will need to create the connection:

  • IP address of the database endpoint 
  • Port (should be 1521 if mTLS has been disabled as suggested at the start of the post)
  • ssh key file name (should have a .pub suffix)
  • The connection string of the Autonomous Database service that you want to use to connect to the database (remember to copy the TLS string if not using mTLS)

TLS Connection string

Create Session

  • On the pop-up page:
    • select SSH port forwarding session under Session type
    • change the name if you like
    • enter the IP address of the private endpoint you saved earlier
    • change the Port to 1521
    • upload the .pub keyfile
    • click Create session

Enter session settings

  • Once the session state is Active, click the three dots at the right and click Copy SSH command on the menu

List of sessions

  • Paste the copied command into a terminal session on your machine and change the privateKey name and localPort, and press enter
ssh -i <privateKey> -N -L <localPort>:10.0.1.42:1521 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaaei...@host.bastion.eu-frankfurt-1.oci.oraclecloud.com
  • Go to Oracle SQL Developer and add New Database Connection
  • Enter your connection details:
    • enter the name of your connection
    • enter the username, for ADB it may be admin
    • enter the admin password
    • optionally, check the Save Password box
    • under Connection Type select Custom JDBC
    • in the Custom JDBC URL box enter jdbc:oracle:thin:@ and then paste the connection string saved earlier and replace the host name with localhost (see example below screenshot)
      (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=localhost))(connect_data=(service_name=xyz_connect_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
    • click Test

SQL Developer connection settings

  • The Status at the bottom of the pop-up should show Success after which you can click Connect which connects you to the database!

Summary

Securing access to your data is so important - but it can add some complexity when trying to make a connection. Hopefully, these four options help you connect to your Autonomous Database! For more information, check out the Autonomous Database documentation.

Quentin Jansen

Cloud Solutions Architect

Quentin is a solutions architect with twenty five years experience in the IT industry having worked in all areas from infrastructure and operations to design and development, within the finance, communications and technology sectors.


Previous Post

Essbase 21.4.3.0.0 Release Update Revision is available

Tanya Heise | 1 min read

Next Post


How to Take Actions or Get Notified When Scheduler Jobs Complete in Autonomous Database

Can Tuzla | 4 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider