Connecting Public OAC to private ADW using PAC

 I have an OAC (Oracle Analytics Cloud) instance that is defined as Public Network Access and an ADW (Autonomous DataWarehouse) that is defined on Private Subnet in aVCN (Virtual Cloud Network). I want my OAC to connect to that private ADW.

There are 2 methods I can use to access the private ADW:

1. Using RDG (Remote Data Gateway), used as a bastion server that has access to the private ADW. The RDG is listening to the OAC instance and returns encrypted query results from private or on-prem. databases to the OAC instance.

2. Using PAC (Private Access Channel)  I can configure on the OAC (at the moment only the Enterprise Edition).

In this post I will cover the second option of PAC.

If you want to skip the rest of this post, the important part, beyond the ATeam posts mentioned bellow is the understanding that the PAC DNS Zone to connect to ADW should be "adb.<region>.oraclecloud.com" (Thank You Gayathri Anand for this tip!), all the rest is just the detailed steps.

Some reading materials on the subject: 

Oracle Documentation

ATeam blog: enabling oracle analytics cloud private access channel  and Ateam blog: connecting to private data sources using oracle analytics cloud private access channel as part of the OAC PAC Series

 

Initial state: 

OAC Public Network Access

VCN, named bdahab_vcn configured with Private Subnet named Private Subnet-bdahab_vcn (those are easily configured manually or using a wizard).

ADW instance configured with the above VCN and Private Subnet:

In my case I didn't follow best practice and my OAC instance and ADW are in the same compartment. It doesn't really meter, as long they are in the same cloud region.

Unlike the example in Ateam blog: connecting to private data sources using oracle analytics cloud private access channel, in my case the PAC and the ADW are on the same subnet, I will talk about it later here.

Steps:

If your OAC instance doesn't have a PAC (Private Access Channel), create / configure a PAC.

When entering the Wizard the default is not enough:

We must add the specific DNS Zone of the ADW instance.

In the ADW setting check the Private Endpoint URL, and copy the suffix. This is the DNS entry we need. 

In my case it is: adb.eu-frankfurt-1.oraclecloud.com (the format is adb.<region>.oraclecloud.com)

Back in OAC PAC configuration, we add it as the DNS Zone

The first time configure phase of PAC might take over 30 minutes (updating it, if needed, is much faster).

 

 

The last network setup step is setting the Security List for Private Subnet.

Under: Networking >> Virtual Cloud Networks menu find the relevant network (bdahab_vcn in my case), click on it and enter the private subnet (Private Subnet-bdahab_vcn, in my case).

There, copy the Private Subnet IPv4 CIDR Block and edit or add a security list:

 

In that security list I will add one ingress and one egress rule. In both cases I use the ADW port (default 1522) and the Private Subnet CIDR block.

Ingress rule:

Egress Rule:

That is all.

 Testing:

Now I am ready to test. To connect the ADW I need it's wallet. It can be found in the ADW setup under Service Console >> Administration >> Download Client Credentials (Wallet)

Create a password for the wallet and download it.

Now in OAC I can create a new connection, with that wallet:

Create Connection

 

Select ADW

 

Fill the details and select the wallet zip file

After the connection successfully created, I can create datasets with it:

For working with RPD you might want to set the connectin in OAC under Console >> Connections.

First extract from the above mentioned ADW wallet zip file the the cwallet.sso file. While you are there check the tnsnames.ora file as well.

Before creating a connection upload the cwallet.sso as the wallet from the top right menu. 

Now we can create the connection based on the data in the tnsnames.ora file using the enable ssl option. You might want to refrain from using the high option of the tns in the file. It is usually less fitting concurrent analytics needs.

You might want to see additional info here: Ateam blog: Connecting Oracle Analytics Cloud Version 5.1+ to the Autonomous Data Warehouse (last sections) and Ateam blog: Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse to see detaied description of the last subject.

PAC and ADW om separate Private Subnets

If you created a separate Private subnets for ADW and for PAC, you should follow the guidlines of the blog here (Ateam blog: connecting to private data sources using oracle analytics cloud private access channel). The only difference from my case is creating egress rule in one security list and Ingress in another, while using the relevant IPV4 CIDR block values of the subnets.