Associate Private Views to the VCN Private Resolvers with ATP Database and Oracle SQL Developer in Different VCNs
In Oracle Cloud Infrastructure (OCI), associating private views to the Virtual Cloud Network (VCN) Private Resolver involves setting up DNS configurations to ensure that specific DNS queries within your VCN resolve to private IP addresses according to your defined DNS records. This setup enhances the management and resolution of internal domain names.
A Private View is a collection of DNS zones and records that can be associated with VCNs to resolve domain names internally.
In this tutorial, we will combine this with a use-case to create an Autonomous Transaction Processing Database (ATP) and perform basic network connectivity tests and tests with the Oracle SQL Developer application from within the same VCN, and from another VCN. This way we can see how the Virtual Cloud Network (VCN) Private Resolver works and how to configure the Private Views.
The steps
- [ ] STEP 01: Create the VCN environments
- [ ] STEP 02: Create an Autonomous Transaction Processing Database (with a private access endpoint)
- [ ] STEP 03: Download the Credentials Wallet
- [ ] STEP 04: Use Oracle SQL Developer to test the Database connection from the same VCN
- [ ] STEP 05: Use Oracle SQL Developer to test the Database connection from a different VCN
- [ ] STEP 06: Associated private views to the VCN Private resolver
- [ ] STEP 07: Use Oracle SQL Developer to test the Database connection from a different VCN (Associated private views to the VCN Private resolver)
STEP 01 - Create the VCN environments
To create an Autonomous Transaction Processing (ATP) Database and test the connections across multiple VCNs we need to have multiple VCNs configured and because the networks inside the VCNs need to communicate with each other we need to attach the VCNs to a Dynamic Routing Gateway.
Creating the VCN environments is out of the scope of this tutorial, but if you want to create multiple VCNs with Hub and Spoke Routing I have explained how to do this in [this article].
In the example that is illustrated below, I have created
- 1 x VCN with a public and private subnet
- 1 x VCN with a private subnet
- 1 x DRG that has both VCNs attached
- 1 x Windows Instance in the public subnet of the Hub VCN
- 1 x Windows Instance in the private subnet of the Spoke VCN
The goal is to deploy the ATP Database in the private subnet of the Hub VCN and to test the connection from both Windows instances using Oracle SQL Developer.
STEP 02 - Create an Autonomous Transaction Processing Database with a private access endpoint
- Click in the left upper corner of the hamburger menu.
- Click on Databases.
- Click on Autonomous Transaction Processing.
- Click on the Create Autonomous Database button.
- Specify a Display Name.
- Specify a Database Name.
- Choose the workload type to be Transaction Processing.
- Scroll down.
- Choose the database type to be Serverless.
- Scroll down.
- Specify and confirm a password.
- Scroll down.
- For the network access select the Access type Private endpoint access only.
- Select the VCN you want to deploy the ATP Database in.
- Select the subnet you want to deploy the ATP Database in.
- Check the box to enable mutual TLS (mTLS) authentication.
- Scroll down.
- Click on the Create Autonomous Database button.
- When the ATP Database is created you will see that the status is AVAILABLE.
- Scroll down.
- Notice the Private IP address of the ATP Database. Make a note of this as we will be using this later to test the connection.
- We also need the Private Endpoint URL for testing. Click on Show.
- Make a note of the Private Endpoint URL as we will be using this later to test the connection.
- Scroll up.
- In the picture below you will see an illustration of what we have created so far.
STEP 03 - Download the Credentials Wallet
- Click on the Database connection button.
- Select the Wallet type to be the Instance Wallet.
- Click on the Download wallet button to download the zip file with the wallet.
- Click on the Close button.
- You will need this wallet file for (testing) the authentication.
STEP 04 - Use Oracle SQL Developer to test the Database connection from the same VCN
Before we are going to use the Oracle SQL Developer application to test the Database connection we are first going to test the network connectivity towards the ATP Database. We first use the Windows Instance (client) that is hosted in the SAME VCN where the ATP Database is deployed.
- Open a Command terminal window.
- Type in the command `telnet 172.16.0.64 1522`.
- Because we are using a secured connection the database will have to listen to TCP port 1522.
- Press Enter.
- Notice that the connection is accepted. So from a network level, we can confirm that the connection works using the IP address from the SAME VCN.
- Now let's do the same test again but now we will use the Private Endpoint URL.
- Type in the command `telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522`.
- Notice that the connection is accepted. So from a network level, we can confirm that the connection works using the Private Endpoint URL. from the SAME VCN.
- Download [Oracle SQL Developer](https://www.oracle.com/database/sqldeveloper/technologies/download/) and open the application.
- Right-click on Oracle Connections.
- Click on New Connection.
- Specify a name.
- Specify the username (admin) for the database.
- Specify the password for the database.
- Select the Connection type to be Cloud Wallet.
- Click on the Browse button to select the (downloaded .zip file) Cloud Wallet.
- Make sure the Cloud Wallet file is selected in the path.
- Select the Service to be ihatp_tpurgent.
- Click on the Save button.
- Notice the new connection is saved.
- Click on the Test button.
- Notice the status will display Success.
- Click on the Connect button.
- The username is already specified.
- Specify the password.
- Click on the OK button.
- Notice that the Oracle SQL Developer application is now connected to the ATP Database.
- Click on the + sign to expand the ATP Database connection.
- In the picture below you will see an illustration of what we have created so far.
STEP 05 - Use Oracle SQL Developer to test the Database connection from a different VCN
Not that we have tested the ATP Database with the Oracle SQL Developer application in the SAME VCN, we will not do the same test, but now the Oracle SQL Developer application will be in a DIFFERENT VCN.
- Let's perform the same network connectivity tests we did before, but now from a different VCN.
- Open a Command terminal window.
- Type in the command `telnet 172.16.0.64 1522`.
- Because we are using a secured connection the database will have to listen to TCP port 1522.
- Press Enter.
- Notice that the connection is accepted. So from a network level, we can confirm that the connection works using the IP address from the OTHER VCN.
- Now let's do the same test again but now we will use the Private Endpoint URL.
- Type in the command `telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522`.
- Notice that the connection is NOT accepted. So from a network level, we can confirm that the connection does NOT work using the Private Endpoint URL from the OTHER VCN.
Connecting To zps@ilns.adb.eu-frankfurt-1.oraclecloud.com...Could not open a connection to the host, on port 1522: Connection failed.
- Specify a name.
- Specify the username (admin) for the database.
- Specify the password for the database.
- Select the Connection type to be Cloud Wallet.
- Click on the Browse button to select the (downloaded .zip file) Cloud Wallet.
- Make sure the Cloud Wallet file is selected in the path.
- Select the Service to be ihatp_tpurgent.
- Click on the Save button.
- Notice the new connection is saved.
- Click on the Test button.
- Notice that the application is trying to connect.
- Notice that the connection fails with the following error:
Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=zdt8muWqQs+N+gWfgIDCTg==)
So the question arises:
- Why can we successfully connect with the Oracle SQL Developer application using the cloud wallet in the SAME VCN and not in ANOTHER VCN?
- Why can we successfully test the network connection with telnet using the IP address of the ATP Database, and not the Private Endpoint URL?
- In the picture below you will see an illustration of what we have created so far.
By default, each VCN creates a (Private) DNS resolver with DNS host entries for components that are INSIDE that VCN. DNS host entries from OTHER VCNs are not allowed by default.
The connection wallet file is using the Private Endpoint URL to establish the connection and NOT the IP address.
This means that when we tested the ATP Database connection with the Oracle SQL Developer Application from the same VCN we were able to successfully connect to the ATP Database.
But when we tested the ATP Database connection with the Oracle SQL Developer Application from ANOTHER VCN we were NOT able to connect to the ATP Database.
This also answers the question of why the telnet IP address connectivity test was successful on both VCNs and the Private Endpoint URL was not.
To fix the issue we need to allow the associated private views to the VCN Private resolver and allow both VCNs to resolve (hosts or endpoints) in the other VCN.
STEP 06 - Associated private views to the VCN Private resolver
- Click on the hamburger menu in the upper left corner of the OCI console.
- Click on Networking.
- Click on Virtual cloud networking.
- Click on the VCN where the ATP Database is hosted in.
- Click on the DNS Resolver of the VCN.
- Scroll down.
- Click on the Manage private views button.
- Select the OTHER VCN to allow so that that VCN can resolve DNS host names in THIS VCN.
- Click on the Save changes button.
- Notice that the Private Resolver status will change to UPDATING.
- Notice that the Private Resolver status will change from UPDATING to ACTIVE.
* You might have to refresh the browser if the Console does not update automatically.
- Notice that the other VCN is added to the Private Views.
- Scroll up.
- Click on the Virtual cloud networks breadcrumbs menu to return to the VCN page.
- Click on the other VCN (where the ATP Database is NOT hosted in).
- Click on the DNS Resolver of the VCN.
- Scroll down.
- Click on the Manage private views button.
- Select the OTHER VCN to allow so that that VCN can resolve DNS host names in THIS VCN.
- Click on the Save changes button.
- Notice that the Private Resolver status will change to UPDATING.
- Notice that the Private Resolver status will change from UPDATING to ACTIVE.
* You might have to refresh the browser if the Console does not update automatically.
- Notice that the other VCN is added to the Private Views.
- In the picture below you will see an illustration of what we have created so far.
STEP 07 - Use Oracle SQL Developer to test the Database connection from a different VCN Associated private views to the VCN Private resolver
- Now let's do the same test again with the Private Endpoint URL.
- Type in the command `telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522`.
- Notice that the connection is now accepted. So from a network level, we can confirm that the connection works using the Private Endpoint URL. from the OTHER VCN.
- With the Oracle SQL Developer application still open click on the Test button again.
- Notice the status will display Success.
- Click on the Connect button.
- The username is already specified.
- Specify the password.
- Click on the OK button.
- Notice that the Oracle SQL Developer application is now connected to the ATP Database.
- Click on the + sign to expand the ATP Database connection.
- In the picture below you will see an illustration of what we have created so far.
Conclusion
In this tutorial, we have created a Private View inside the Virtual Cloud Network (VCN) Private Resolver to allow components from one VCN to resolve DNS records from another VCN component.
We explained a use case where we will create an Autonomous Transaction Processing Database (ATP) and perform some basic network connectivity tests and tests with the Oracle SQL Developer application from within the same VCN, and from another VCN.
We used the Wallet file (that uses the Private Access DNS name or the ATP Database) and tested the ATP Database connection from the same and a different VCN. To allow connectivity from a different VCN (using the private URL with an FQDN) the Private Resolvers needed to be configured.
- In the picture below you will see an illustration of what we have created so far.