GoldenGate Cloud Service (GGCS): Enable SSL SQLNet on GGCS to connect to Oracle Database

Introduction

This document will walk you through how to enable SSL SQLNet connection on GGCS to connect to an Oracle Database. Enabling SSL communication between GGCS and the Oracle database is achieved by configuring credential wallet on both GGCS and the database which will store certificates and by modifying the configuration files used for SQLNet communication.

The certificate to be used for SSL communication requires to be signed by a trusted certificate authority (CA), however, in this article for ease of purposes we will use a self-signed certificates.

Creating self-signed certificates can be accomplished by using the orapki utility. By default, Oracle database server comes with orapki, however GGCS doesn’t have the orapki utility, so we will need to create the SSL SQLNet credential wallet for GGCS on the database server and transfer the wallet files to the GGCS server.

For more details on Oracle’s orapki, please check the following Oracle Documentation link:

The concepts, scripts and information presented in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

Main Article

For this article, the following examples or assumptions were used for the provisioning process:

  • GGCS Server on the Oracle Cloud: east-ggcs-vm-mp
  • GGCS Server $GGHOME: /u01/app/oracle/gghome
  • Database Server on the Oracle Cloud: east-comp-vm-mp
  • Oracle Database CDB User: c##ggsys
  • Oracle Database PDB User: ggadmin
  • Database Server $ORACLE_HOME: /u01/app/oracle/product/12.1.0.2/db_1/

High Level Steps for GGCS SSL SQLNet Setup

The following are the high-level steps for enabling SSL SQLNet connection from GGCS to an Oracle Database Server:

  • Create the Wallet to be used by the Database Server
  • Add the self-signed certificate for the Database Server
  • Export the Database Server self-signed certificate into a file
  • Create the  Wallet to be used by GGCS Server
  • Add the self-signed certificate for the GGCS server
  • Export the GGCS Server self-signed certificate into a file
  • Import the Database Server certificate into the GGCS Server wallet and vice-versa
  • Configure the SQLNet and Listener configuration files on the Database Server and restart the Listener process
  • Transfer the wallet files created for GGCS into the GGCS server
  • Configure the SQLNet configuration files (sqlnet.ora and tnsnames.ora) on the GGCS server
  • Test SSL Connectivity to the Database Server via SQLPlus and GGCSI DBLogin

Create the Wallet to be used by the Database Server

To create the wallet for the Database server, we need to login to the Database server and create a directory for the wallet and execute the orapki utility..

mpapio@borg:~$ ssh -i mp_opc_ssh_key oracle@east-comp-vm-mp
[oracle@east-comp-vm-mp ~]$ mkdir -p $ORACLE_HOME/server_wallet
[oracle@east-comp-vm-mp ~]$ orapki wallet create -wallet "$ORACLE_HOME/server_wallet" -pwd WalletPasswd123 -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]

Add the self-signed certificate for the Database Server

Now, we need to add the self-signed certificate to be used for the database server, again this is done via the orapki utility, and then verify it via the display wallet command to make sure we have added the certificate successfully. For the Domain or Common Name (CN) of the Database Server Certificate we will use it’s hostname (east-comp-vm).

[oracle@east-comp-vm-mp ~]$ orapki wallet add -wallet "$ORACLE_HOME/server_wallet" -pwd Welcome123 -dn "CN=east-comp-vm-mp" -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ orapki wallet display -wallet "$ORACLE_HOME/server_wallet" -pwd Welcome123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject: CN=east-comp-vm-mp
Trusted Certificates: 
Subject: CN=east-comp-vm-mp
[oracle@east-comp-vm-mp ~]$

Export the Database Server self-signed certificate into a file

Now, we need to export the certificate from the wallet into a file and verify that the export was successful by listing the content of the exported file. We will be need this file to import this certificate to the GGCS wallet once we created it.

[oracle@east-comp-vm-mp ~]$ orapki wallet export -wallet "$ORACLE_HOME/server_wallet" -pwd Welcome123 -dn "CN=east-comp-vm-mp" -cert /tmp/east-comp-vm-mp-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ cat /tmp/east-comp-vm-mp-certificate.crt
-----BEGIN CERTIFICATE-----
MIIBozCCAQwCAQAwDQYJKoZIhvcNAQEEBQAwGjEYMBYGA1UEAxMPZWFzdC1jb21wLXZtLW1wMB4X
DTE3MTIxNjAzMzkxMloXDTI3MTIxNDAzMzkxMlowGjEYMBYGA1UEAxMPZWFzdC1jb21wLXZtLW1w
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDCYKlDDyW24ahF+y1851EOuo71XD2RyNp0xgQs
kzCFXYufrEF2CJnjs5US+2E4g7XqTVGNi3aqGQsnrpzwcRHed5X0SkB6N4gs2u493p72nkI1xhgo
+yxkjDewkvLudSX/sCAUyZOZUY3mcTQwj4v/13ZbwbXZgwZ61seVGGlfHwIDAQABMA0GCSqGSIb3
DQEBBAUAA4GBAAx6otxVcwehXexNcyQ/AXo+IVa/uHxGMqQjgiLE0vpR9qb5kgIdCXzEIJc+l8B+
nL8datX8+gf13Q2xiJ3nLq+NmXNIajAPuUFSZD+Atc+ZJowZqIrp4nTKLAYC6OHrwl89q0NGCfJC
PozNLpBbjZy9BUFtxsbN/5xq9cN9fvDO
-----END CERTIFICATE-----[oracle@east-comp-vm-mp ~]$

Create the  Wallet to be used by GGCS Server

Now, we need to create a wallet to be used by GGCS server. The orapki utility by default is not available on GGCS, so we would need to create the wallet on the database server and name the directory differently, in this case we will use “client_wallet” to house the wallet for GGCS server.

[oracle@east-comp-vm-mp ~]$ mkdir -p $ORACLE_HOME/client_wallet
[oracle@east-comp-vm-mp ~]$ orapki wallet create -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123 -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$

Add the self-signed certificate for the GGCS server

Now, we need to add the self-signed certificate to be used for the GGCS server, again this is done via the orapki utility, and then verify it via the display wallet command to make sure we have added the certificate successfully. For the Domain or Common Name (CN) for the GGCS Server Certificate we will use GGCS Server hostname (east-ggcs-vm-mp).

oracle@east-comp-vm-mp ~]$ orapki wallet add -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123 -dn "CN=east-ggcs-vm-mp" -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ orapki wallet display -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject: CN=east-ggcs-vm-mp
Trusted Certificates: 
Subject: CN=east-ggcs-vm-mp
[oracle@east-comp-vm-mp ~]$

Export the GGCS Server self-signed certificate into a file

Now, we need to export the certificate from the wallet into a file and verify that the export was successful by listing the content of the exported file. We will be need this file to import this certificate to the Database Server wallet.

[oracle@east-comp-vm-mp ~]$ orapki wallet export -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123 -dn "CN=east-ggcs-vm-mp" -cert /tmp/east-ggcs-vm-mp-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ cat /tmp/east-ggcs-vm-mp-certificate.crt
-----BEGIN CERTIFICATE-----
MIIBozCCAQwCAQAwDQYJKoZIhvcNAQEEBQAwGjEYMBYGA1UEAxMPZWFzdC1nZ2NzLXZtLW1wMB4X
DTE3MTIxNjAzNTkzN1oXDTI3MTIxNDAzNTkzN1owGjEYMBYGA1UEAxMPZWFzdC1nZ2NzLXZtLW1w
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCZERJjKiuJcaHFBh+Jh1p9HHMF+M/d5JNQC6RB
QS6QMkm66w1o6jluR3ZUt29hkQVKEQ+lMnfobZ+A1hb1WNHM12FcUcWtUmOaKplxkdgs+X6iqxVF
VNfJYfj+TG7dXWTM/7rNSS0TPpo8SerM8d16qJQW7h0zalk6U72jLrTD2QIDAQABMA0GCSqGSIb3
DQEBBAUAA4GBACyNWRaRSe+1sz0vekt1++4mTk4cgVn3RyMszoTqHHHN/MMng0YqYv6rPFGtmFpJ
AtFumVKogGngYxDrQXRz3KAPovkqb+YjxDtM+uc/bbKQOLLcRwXee0zqCQBXYcLyDEKPscX6U1Wm
UIo+vS5YhSrgHNWhlH7tpb+Fh700DPBy
-----END CERTIFICATE-----[oracle@east-comp-vm-mp ~]$

Import the Database Server certificate into the GGCS Server wallet and vice-versa

Now, we need to import the Database Server certificate file that we created (/tmp/east-comp-vm-mp-certificate.crt) into the GGCS wallet, and then import the GGCS server certificate file that we also created into the Database Server wallet. Check the contents of each wallet via orapki display command and it should have the entry for both certificates on each wallet under the “Trusted Certificates” section.

[oracle@east-comp-vm-mp ~]$ orapki wallet add -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123 -trusted_cert -cert /tmp/east-comp-vm-mp-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ orapki wallet display -wallet "$ORACLE_HOME/client_wallet" -pwd Welcome123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject: CN=east-ggcs-vm-mp
Trusted Certificates: 
Subject: CN=east-ggcs-vm-mp
Subject: CN=east-comp-vm-mp
[oracle@east-comp-vm-mp ~]$
[oracle@east-comp-vm-mp ~]$ orapki wallet add -wallet "$ORACLE_HOME/server_wallet" -pwd Welcome123 -trusted_cert -cert /tmp/east-ggcs-vm-mp-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

[oracle@east-comp-vm-mp ~]$ orapki wallet display -wallet "$ORACLE_HOME/server_wallet" -pwd Welcome123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject: CN=east-comp-vm-mp
Trusted Certificates: 
Subject: CN=east-ggcs-vm-mp
Subject: CN=east-comp-vm-mp
[oracle@east-comp-vm-mp ~]$

Configure the SQLNet and Listener configuration files on the Database Server and restart the Listener process

Now, we need to edit the sqlnet.ora file and listener.ora to enable SSL communication.

Modify the sqlnet.ora and make sure to add the following entry:

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION=
(SOURCE=
(METHOD=file) (METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/server_wallet)))

Modify the listener.ora and make sure to add TCPS entry and assign a specific port under the LISTENER section to enable SSL, this is the protocol needed to tell the listener to use SSL. Be sure also to add the WALLET_LOCATION entry. Here’s the entry in listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = east-comp-vm-mp)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = east-comp-vm-mp)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=file) (METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/server_wallet)))

Once you modified the sqlnet.ora nd listener.ora file, you can restart the listener process on the Database server.

Transfer the wallet files created for GGCS into the GGCS server

Now, we need to transfer the GGCS wallet files that we created on the Database server to the GGCS server. Will use tar and scp to transfer it to GGCS server. Since opc is the only user allowed into the GGCS server, we would need to transfer the client_wallet.tar file into a temporary location. Then, we would need to logon to GGCS server as opc and switch user to “oracle” and untar/copy it to GGCS $GGHOME directory.

[oracle@east-comp-vm-mp ~]$ cd $ORACLE_HOME
[oracle@east-comp-vm-mp db_1]$ tar cvf client_wallet.tar client_wallet
client_wallet/
client_wallet/cwallet.sso
client_wallet/ewallet.p12.lck
client_wallet/ewallet.p12
client_wallet/cwallet.sso.lck
[oracle@east-comp-vm-mp db_1]$ 
[oracle@east-comp-vm-mp db_1]$ scp -r -i mp_opc_ssh_key $ORACLE_HOME/client_wallet.tar opc@east-ggcs-vm-mp:/tmp
client_wallet.tar 100% 10KB 10.0KB/s 00:00 
[oracle@east-comp-vm-mp db_1]$
[oracle@east-comp-vm-mp db_1]$ ssh -i mp_opc_ssh_key opc@east-ggcs-vm-mp
[opc@east-ggcs-vm-mp-ggcs-1 ~]$
[opc@east-ggcs-vm-mp-ggcs-1 ~]$ sudo su - oracle 
[oracle@east-ggcs-vm-mp-ggcs-1 ~]$ cd $GGHOME
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ tar xvf /tmp/client_wallet.tar
client_wallet/
client_wallet/cwallet.sso
client_wallet/ewallet.p12.lck
client_wallet/ewallet.p12
client_wallet/cwallet.sso.lck
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ ls -ltr $GGHOME/client_wallet
total 8
-rw-r----- 1 oracle oinstall 0 Dec 15 22:49 ewallet.p12.lck
-rw-r----- 1 oracle oinstall 0 Dec 15 22:49 cwallet.sso.lck
-rw------- 1 oracle oinstall 2920 Dec 15 23:08 ewallet.p12
-rw------- 1 oracle oinstall 2965 Dec 15 23:08 cwallet.sso
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$

 

Configure the SQLNet configuration files (sqlnet.ora and tnsnames.ora) on the GGCS server

Now, we need to edit the sqlnet.ora file and listener.ora to enable SSL communication. On GGCS server the SQLNet configuration files for Oracle 12c are located in the /u01/app/oracle/oci/network/admin directory.

Modify the sqlnet.ora and make sure to add the following entry:

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION=
(SOURCE=
(METHOD=file) (METHOD_DATA=(DIRECTORY=/u01/app/oracle/gghome/client_wallet)))

Now, modify the tnsnames.ora and add a TNS service that points the connection to the Database server that uses the TCPS protocol and it’s specific TCP port. For my tnsnames.ora, I have added the TNS service names ssl-east-comp-vm-mp-orcl for the CDB database and ssl-east-comp-vm-mp-pdborcl for the PDB database.

Here’s what my tnsnames.ora looks like:

east-comp-vm-mp-orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = east-comp-vm-mp)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
east-comp-vm-mp-pdborcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = east-comp-vm-mp)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
ssl-east-comp-vm-mp-orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = east-comp-vm-mp)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ssl-east-comp-vm-mp-pdborcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = east-comp-vm-mp)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)

Test SSL Connectivity to the Database Server via SQLPlus and GGCSI DBLogin

Once the tnsnames.ora and sqlnet.ora file has been modified on GGCS server, now you are ready to connect via SSL.To test and verify SSL connectivity of SQLNet from GGCS to the Database server, it can be done via SQLPlus and GGCSCI.

From GGCS Server, let’s use SQLPlus and connect to the Container Database using c##ggsys user and verify the network protocol of the session. We have to use the TNS service name ssl-east-comp-vm-mp-orcl to use SSL and use the TCPS protocol:

[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ 
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ sqlplus c##ggsys@ssl-east-comp-vm-mp-orcl/Welcome123

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 16 00:44:59 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sat Dec 16 2017 00:42:55 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> select sys_context('userenv','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$

Now let’s connect to the Pluggable database via SQLPlus again using ggadmin user and verify again the network protocol of the session. We have to use the TNS service name ssl-east-comp-vm-mp-pdborcl to use SSL and use the TCPS protocol:

[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ 
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ sqlplus ggadmin@ssl-east-comp-vm-mp-pdborcl/Welcome123

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 16 00:48:15 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL
SQL> select sys_context('userenv','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$

Now, let’s use GGSCI via the DBLOGIN command to connect to the database via SSL using the same CDB User and PDB user and using the corresponding TNS service for the CDB and PDB.

Let’s do the CDB connection first:

[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jan 23 2017 23:22:14
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (east-ggcs-vm-mp-ggcs-1) 1> dblogin userid c##ggsys@ssl-east-comp-vm-mp-orcl password Welcome123
Successfully logged into database CDB$ROOT.

GGSCI (east-ggcs-vm-mp-ggcs-1 as c##ggsys@orcl/CDB$ROOT) 2> exit
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$

Finally, let’s connect to the Pluggable Database:

[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jan 23 2017 23:22:14
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (east-ggcs-vm-mp-ggcs-1) 1> dblogin userid ggadmin@ssl-east-comp-vm-mp-pdborcl password Welcome123
Successfully logged into database PDBORCL.

GGSCI (east-ggcs-vm-mp-ggcs-1 as ggadmin@orcl/PDBORCL) 2> exit
[oracle@east-ggcs-vm-mp-ggcs-1 gghome]$

Once you verify connectivity via SSL on both SQLPlus and GGCSI DBLOGIN, you can proceed on configuring your GoldenGate replication via SSL SQLNet connection.

Summary

Hopefully, this article has provided an overview on how to enable SSL SQLNet communication on GGCS to connect to Oracle Database

For more detailed information and advanced configuration of Secure Sockets Layer (SSL) authentication on Oracle 12c, check the Database Administration Guide under the Database Security Guide Section.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

References

Reference the Oracle GoldenGate Cloud Service Documentation for additional information on GGCS.

Reference the Oracle GoldenGate 12c Reference and Administration Guide for additional information GoldenGate

Add Your Comment