TL/DR: To use OCI Database Tools (DBTools) Connections to connect to ADB with a public IP and access control list (ACL), DBTools requires the use of a DBTools Private Endpoint (PE). DBTools connections setup with a PE can route traffic to the ADB instance through a Service Gateway (preferred) or through a NAT gateway. The ACL would be configured to accept traffic from the VCN where the PE is created with the former or from the public IP of the NAT gateway with the latter.
Keep reading for more information and lots of screenshots!
Introduction
In Oracle Cloud Infrastructure (OCI), an Autonomous Database - Serverless (ADB-S) instance can be setup with different types of network access. At the time of this writing ADB running in OCI supports:
- Public IP with mTLS
- Public IP with ACL
- Private endpoint access
The DBTools service helps customers create and use connections for any of these networking scenarios. Here is a look at the general Oracle Database use cases DBTools supports with some useful diagrams for each:
In this post I focus on setting up DBTools connections with an ADB-S instance with public IP + ACL. This setup will look something like this:
Prerequisites
This post doesn't cover every click, menu, etc. I assume some resources already exist, including:
- A virtual cloud network (VCN) with a private subnet
- A DBTools PE created in the above subnet
- An OCI Vault with an encryption key where secrets will be stored
- An ADB instance with public IP and ACL enabled
Note: The default security list is fine for what follows but if yours is customized then you may have additional steps not covered here. For example, I did not change anything in the settings since traffic will flow outbound from the PE to the ADB instance.
For visual reference, here are some examples of the above VCN resources.
Here is an example of a DBTools PE created within the above private subnet.
Note the first of the two reverse connection private IP addresses shown above. We will see that again later in this post. (i.e. 10.0.1.27) And finally, here is an example of an ADB-S database instance with public IP and ACL enabled.
Access control list: Enabled then you should be aware that what follows does not apply to that ADB instance. Please only change this setting if you'd like to follow along and you are aware that changing this will likely break any existing connections.The Database Tools Connection
Once you have the resources above created you can setup a DBTools connection. You should create a DBTools connection and associate it with the PE. The connection creation wizard is pretty intuitive if you want to jump right in and get your hands dirty or you can read about the process here:
Here is an example of a DBTools connection setup to point to the ADB database example shown above.
Notice how the connection shows the configured private endpoint. This PE will allow the DBTools service to send SQL statements to the target VCN and this is important for the steps that follow.
Now lets validate or new connection by pressing Validate!
That would have been too easy but the timeout error makes sense. We have a connection resource setup but the ACL has no idea from where the traffic is coming, yet. We will fix the above issue very shortly.
VCN Configuration (Service Gateway)
The PE we created above setup a virtual network interface card (VNIC) in our private subnet but right now the VCN is not configured to send that traffic to ADB. As the title of this section implies, we will setup a Service Gateway in the VCN and then configure a route to allow OCI service traffic to use the gateway.
First, open your VCN in the OCI console and create a Service Gateway.
In this example, I called my Service Gateway jb-dbtools-sgw-test. Furthermore, enable All <region> Service in Oracle Services network which allows traffic from OCI services (like DBTools service) to travel over the Oracle Services Network.
Next, configure the Route Rules for the VCN to use this Service Gateway. Once that is setup you may have something like this:
Autonomous Database ACL Configuration
The last step to connect the network dots is to allow the VCN to speak to ADB. This is done by configuring the ACL and adding a new access control rule. Ignore the public IP address shown here for now, and focus on the VCN OCID.
Click on Add access control rule and then choose the VCN where you previously setup the DBTools PE. You should see an option to pick the human readable name and once the rule is saved, the OCI console will update this to the correct OCID.
With this setup in place we should now be able to validate and use the DBTools connection for real.
Validate the Connection and Prove It
Return to your DBTools connection and once again press Validate and you should be presented with something similar to the following.
A successful validation tells you that the DBTools service was able to connect to the ADB database at the other end of the connection. If you followed along based on the setup above, then this traffic is further sent to the ADB instance over the Oracle Services Network from the private IP address of the PE VNIC in your VCN.
Lets gather some proof that we are indeed connecting from the PE. First open the SQL Worksheet using this DBTools connection and run the following SQL.
select sys_context('userenv', 'ip_address') from dual;When you execute this statement you should see something similar to the following output in the SQL Worksheet.
Remember when I said we should note the first of the two private IP addresses created with the PE. (i.e. 10.0.1.27) In the example above you can see that ADB is communicating with the private IP of our DBTools PE.
Cool!
So, what about the NAT gateway option? This is very similar to the Service Gateway. Let's take a look.
Rewind a Little Bit
Let's rewind a little bit to the point where our connection was not able to validate and pretend that we never setup the Service Gateway. If for some reason you must use a NAT gateway then you can do so. Once your VCN, DBTools PE and connection are setup, create a NAT gateway in your VCN.
Notice that when you create a NAT gateway you should get a public IP address. In this example, I got a public address in the 129.15n.nnn.nnn range. I've masked the value shown to protect the innocent but you get the idea.
With the NAT gateway setup you then need to configure a route rule for it. Warning, shamefully copy/pasting screenshots here. This is the same image you saw above.
In this case, notice the first rule listed is for the NAT Gateway and this is the one you need to setup (or something like it) so that your VCN traffic can reach the ADB public IP address via NAT gateway.
Autonomous Database ACL Configuration (Option 2)
Now that you have a NAT gateway setup you need to configure the ACL in your ADB instance to allow traffic from the public IP of your NAT gateway.
Edit the ACL of your database and press Add access control rule and then choose the IP address and enter the public IP of your NAT gateway.
Wrapping Up
Finally, return to your DBTools connection and validate it. It should validate as shown up above and if we want to prove to ourselves that the traffic is coming from the NAT gateway, you know the trick already.
Don't worry, I repeat it here. Open the DBTools SQL Worksheet using your validated DBTools connection and run the following SQL.
select sys_context('userenv', 'ip_address') from dual;When you execute this statement you should see something similar to the following output in the SQL Worksheet.
You can see from the partially redacted example above that the proof we are looking for shows the public IP of a NAT gateway. And that's all there is to getting DBTools connections to work with ADB instances that use an Access Control List.
I hope you found this useful. Until next time.
Cheers!