Configure Oracle database Network connection with SSL/TLS in ODA Machine

February 2, 2022
()

Configure Oracle database Network connection with SSL/TLS in ODA Machine

In a recent post we saw steps to configure Network Encryption using an SSL Certificate Click here to read more. In this blog post, we will configure Network Encryption in ODA X7-2 M machine this is a single node machine running listener under Grid Infrastructure and Database under Oracle Home. So steps will be slightly different. 

Oracle database provides below two options to enable database connection Network Encryption.

1. Native Network Encryption Click here to read more
2. Encryption using SSL/TLS (Secure Socket Layer / Transport Layer Security)

Network admin has provided with a wildcard SSL certificate in pkcs12 format which contains certificate and keys. We will use the same wildcard certificate to configure SSL/TLS connection. To learn more about SSL file format Click here to read more.

Follow the below steps to configure Oracle Database network connection with SSL/TLS:
1. Convert .pfx SSL file into .p12 format
2. Verify wallet 
3. Configure listener.ora in Grid Home
4. Configure sqlnet.ora in Oracle Home
5. Verify SSL configuration is working fine
6. Configure sqlnet.ora file in the client-side 
7. Test SSL connection in the client-side 

Server-Side Configuration


Step 1. Convert .pfx SSL file into .p12 format: Since we have received wildcard SSL certificate in .pfx format we need to convert it into .p12 format which is Oracle Wallet format. Oracle stores all certificates and keys in Oracle Wallet which is .p12 format. Since we are using 11g Oracle home we need to convert .pfx file to .jks format and then .jks file to .p12 format. But if you are in 12.2c or higher version Oracle Home you can convert .pfx format directly to .p12 format.


Oracle 11g Home – As no direct option to convert .pfx to .p12

[oracle@oda-prod1 db_wallet]$ pwd
/u01/app/oracle/admin/db_wallet
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 5597 Dec  6 17:05 dbcert.pfx
[oracle@oda-prod1 db_wallet]$
 
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ orapki wallet pkcs12_to_jks -wallet ./dbcert.pfx -jksKeyStoreLoc ./dbcert.jks -jksKeyStorepwd WelcomE#_1234
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:   WelcomE#_1
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ ls -ltr
total 32
-rw-r--r--. 1 oracle oinstall 5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall 7643 Dec  6 17:07 dbcert.jks
 
[oracle@oda-prod1 db_wallet]$
 
[oracle@oda-prod1 db_wallet]$ orapki wallet create -wallet . -pwd WelcomE#_1234 -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
 
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ ls -ltr
total 44
-rw-r--r--. 1 oracle oinstall  5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall  7643 Dec  6 17:07 dbcert.jks
-rw-------. 1 oracle oinstall 10072 Dec  6 17:08 ewallet.p12
-rw-------. 1 oracle oinstall 10149 Dec  6 17:08 cwallet.sso
[oracle@oda-prod1 db_wallet]$
 
[oracle@oda-prod1 db_wallet]$ orapki wallet jks_to_pkcs12 -wallet . -pwd WelcomE#_1234 -keystore ./dbcert.jks -jkspwd WelcomE#_1234
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
 
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ ls -ltr
total 44
-rw-r--r--. 1 oracle oinstall  5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall  7643 Dec  6 17:07 dbcert.jks
-rw-------. 1 oracle oinstall 10072 Dec  6 17:08 ewallet.p12
-rw-------. 1 oracle oinstall 10149 Dec  6 17:08 cwallet.sso
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$


Oracle 12.2c onwards Home – Direct option available to convert .pfx to .p12

[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$ pwd
/u01/app/oracle/admin/12C_wallet
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$ ls -ltr
total 8
-rw-r--r--. 1 oracle oinstall 5597 Dec 24 16:17 dbcert.pfx
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$ orapki wallet create -wallet . -pwd WelcomE#_1234 -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
 
Operation is successfully completed.
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 5597 Dec 24 16:17 dbcert.pfx
-rw-------. 1 oracle oinstall    0 Dec 24 16:17 ewallet.p12.lck
-rw-------. 1 oracle oinstall  149 Dec 24 16:17 ewallet.p12
-rw-------. 1 oracle oinstall    0 Dec 24 16:17 cwallet.sso.lck
-rw-------. 1 oracle oinstall  194 Dec 24 16:17 cwallet.sso
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$  orapki wallet import_pkcs12 -wallet .  -pkcs12file dbcert.pfx -pkcs12pwd WelcomE#_1234
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
orapki command import_pkcs12 executed successfully.
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$
[oracle@oda-prod1 12C_wallet]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 5597 Dec 24 16:17 dbcert.pfx
-rw-------. 1 oracle oinstall    0 Dec 24 16:17 ewallet.p12.lck
-rw-------. 1 oracle oinstall    0 Dec 24 16:17 cwallet.sso.lck
-rw-------. 1 oracle oinstall 5680 Dec 24 16:18 ewallet.p12
-rw-------. 1 oracle oinstall 5725 Dec 24 16:18 cwallet.sso
[oracle@oda-prod1 12C_wallet]$


Step 2. Verify wallet: Use command orapki wallet display to view all imported certificates. We have 1 User certificate and 2 Trusted certificates i.e. root and intermediate certificates.

[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ pwd
/u01/app/oracle/admin/db_wallet
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ ls -ltr
total 56
-rw-r--r--. 1 oracle oinstall  5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall  7643 Dec  6 17:07 dbcert.jks
-rw-------. 1 oracle oinstall 10072 Dec  6 17:12 ewallet.p12
-rw-------. 1 oracle oinstall 10149 Dec  7 09:12 cwallet.sso
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$ orapki wallet display -wallet .
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Subject:        CN=*.XXXXXXXXXXXXX.com
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=Go Daddy Secure Certificate Authority - G2,OU=http://certs.godaddy.com/repository/,O=GoDaddy.com\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject:        CN=Go Daddy Root Certificate Authority - G2,O=GoDaddy.com\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
[oracle@oda-prod1 db_wallet]$
[oracle@oda-prod1 db_wallet]$


Step 3. Configure listener.ora in Grid Home: Configure the listener to accept SSL/TLS encrypted connections. Edit the “$ORACLE_HOME/network/admin/listener.ora” file, add the wallet location, SSL Port, and Protocol TCPS entry. We will be using 1522 as our SSL port. After listener changes are done. Please note to stop and start listener service and do not reload the listener. Please note that listener is running under Grid Home switch to grid user and change directory to $GRID_HOME/network/admin and add entry “(ADDRESS = (PROTOCOL = TCPS)(HOST = oda-prod01)(PORT = 1522)” only in LISTENER section of listener.ora file.

[grid@oda-prod01 ~]$
[grid@oda-prod01 ~]$ ps -ef|grep tns
root       477     2  0  2020 ?        00:00:00 [netns]
grid      8572     1  0  2021 ?        03:37:16 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER -inherit
grid     15674     1  0  2020 ?        02:12:37 /u01/app/19.0.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
grid     48923 48423  0 12:10 pts/2    00:00:00 grep --color=auto tns
[grid@oda-prod01 ~]$
[grid@oda-prod01 ~]$

[grid@oda-prod01 ~]$
[grid@oda-prod01 ~]$ cd /u01/app/19.0.0.0/grid/network/admin/
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ pwd
/u01/app/19.0.0.0/grid/network/admin
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ vi listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)) 
									(ADDRESS = (PROTOCOL = TCPS)(HOST = oda-prod01)(PORT = 1522))))
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON#lineaddedbyAgent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET#lineaddedbyAgent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON#lineaddedbyAgent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET#lineaddedbyAgenta

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/admin/db_wallet)
     )
   )

SSL_CLIENT_AUTHENTICATION = FALSE
:wq!
[grid@oda-prod01 admin]$

[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): oda-prod01
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ srvctl stop listener
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ srvctl start listener
[grid@oda-prod01 admin]$
[grid@oda-prod01 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): oda-prod01
[grid@oda-prod01 admin]$

[grid@oda-prod01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 14-DEC-2021 12:37:32

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/19.0.0.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/oda-prod01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oda-prod01.local)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                14-DEC-2021 12:37:32
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oda-prod01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oda-prod01.local)(PORT=1522)))
The listener supports no services
The command completed successfully
[grid@oda-prod01 ~]$


Step 4. Configure sqlnet.ora in Oracle Home: On the server, add the wallet location entries into the “$ORACLE_HOME/network/admin/sqlnet.ora” file. We will be doing only SSL ENCRYPTION & SERVER AUTHENTICATION. So CLIENT_AUTHENTICATION parameter set to FALSE. Please note that databases are running under Oracle Home switch to oracle user and change directory to $ORACLE_HOME/network/admin.

[oracle@oda-prod01 admin]$ vi sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/admin/db_wallet)
     )
   )
 
SSL_CLIENT_AUTHENTICATION = FALSE
:wq!
[oracle@oda-prod01 admin]$


Step 5. Verify SSL configuration is working fine: Please note to grant 660 permission on wallet files as wallet files will be used by both grid and oracle OS users. We can use the OpenSSL tool to verify if SSL setup is working fine. Use command openssl s_client and pass Hostname and SSL port.

[oracle@oda-prod01 db_wallet]$ pwd
/u01/app/oracle/admin/db_wallet
[oracle@oda-prod01 db_wallet]$
[oracle@oda-prod01 db_wallet]$ ls -ltr
total 56
-rw-r--r--. 1 oracle oinstall  5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall  7643 Dec  6 17:07 dbcert.jks
-rw-------. 1 oracle oinstall 10072 Dec  6 17:12 ewallet.p12
-rw-------. 1 oracle oinstall 10149 Dec  7 09:12 cwallet.sso
[oracle@oda-prod01 db_wallet]$

[oracle@oda-jed-prod01 db_wallet]$
[oracle@oda-jed-prod01 db_wallet]$ id grid
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1006(asmdba),1005(asmoper),1004(asmadmin),1002(dbaoper)
[oracle@oda-jed-prod01 db_wallet]$ id oracle
uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1006(asmdba),1003(dba),1002(dbaoper)
[oracle@oda-jed-prod01 ~]$
[oracle@oda-jed-prod01 db_wallet]$
[oracle@oda-jed-prod01 db_wallet]$ chmod 660 *wallet*
[oracle@oda-jed-prod01 db_wallet]$

[oracle@oda-prod01 db_wallet]$ ls -ltr
total 56
-rw-r--r--. 1 oracle oinstall  5597 Dec  6 17:05 dbcert.pfx
-rw-------. 1 oracle oinstall  7643 Dec  6 17:07 dbcert.jks
-rw-rw---- 1 oracle oinstall 10072 Dec 14 09:57 ewallet.p12
-rw-rw---- 1 oracle oinstall 10149 Dec 14 09:57 cwallet.sso
[oracle@oda-prod01 db_wallet]$

[oracle@oda-prod01 db_wallet]$ openssl s_client -connect oda-prod01:1522
CONNECTED(00000003)
depth=2 C = US, ST = Arizona, L = Scottsdale, O = "GoDaddy.com, Inc.", CN = Go Daddy Root Certificate Authority - G2
verify return:1
depth=1 C = US, ST = Arizona, L = Scottsdale, O = "GoDaddy.com, Inc.", OU = http://certs.godaddy.com/repository/, CN = Go Daddy Secure Certificate Authority - G2
verify return:1
depth=0 CN = *.XXXXXXXXXXXX.com
verify return:1
No client certificate CA names sent
---
SSL handshake has read 4051 bytes and written 615 bytes
---
New, TLSv1/SSLv3, Cipher is AES256-SHA
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1
    Cipher    : AES256-SHA
    Session-ID:
    Session-ID-ctx:
    Key-Arg   : None
    Krb5 Principal: None
    PSK identity: None
    PSK identity hint: None
    Start Time: 1638862116


Client-Side Configuration


Step 6. Configure sqlnet.ora file on the client-side: Copy same .p21 and .sso files to Client or Application machine and edit the sqlnet.ora file with wallet location and with parameter SSL_CLIENT_AUTHENTICATION = FALSE.

D:\Oracle\admin\db_wallet>
D:\Oracle\admin\db_wallet>dir
 Volume in drive D is HDD 1
 Volume Serial Number is EAA6-BA75
 
 Directory of D:\Oracle\admin\db_wallet
 
12/07/2021  12:46 PM    <DIR>          .
12/07/2021  12:46 PM    <DIR>          ..
12/12/2021  01:46 PM             6,181 cwallet.sso
12/07/2021  12:41 PM             6,104 ewallet.p12
               2 File(s)         12,285 bytes
               2 Dir(s)  118,162,894,848 bytes free
 
D:\Oracle\admin\db_wallet>
D:\Oracle\admin\db_wallet>
 
D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>
D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>type sqlnet.ora
 sqlnet.ora Network Configuration File: D:\Oracle\product\11.2.0.4\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
 
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
 
 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES= (NTS)
 
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\Oracle\admin\db_wallet)
     )
   )
 
SSL_CLIENT_AUTHENTICATION = FALSE
D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>
D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>


Step 7. Test SSL connection on the client-side: Once all the above setups are done. We are ready to make an SSL connection with the database. Please note we need to use SSL Port 1522 & TCPS protocol in the connection string. Please note Step 6 changing permission to 660 is a very important step, if you miss step 6 you will receive “ORA-28759: failure to open file” while doing testing.

D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>
D:\Oracle\product\11.2.0.4\db_1\NETWORK\ADMIN>sqlplus app_user/test123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=oda-prod01)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=testdb)))
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 24 19:34:51 2021
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
 
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
 
SQL>
SQL> show user
USER is "APP_USER"
SQL>

################# 660 Permission is not set ##############################

[oracle@oda-prod01 db_wallet]$
[oracle@oda-prod01 db_wallet]$ sqlplus app_user/test123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=oda-prod01)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=testdb)))

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 13 18:55:24 2021

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

ERROR:
ORA-28759: failure to open file


Enter user-name: ^C
[oracle@oda-prod01 db_wallet]$


References:  https://www.oracle.com/technetwork/topics/wp-oracle-jdbc-thin-ssl-130128.pdf 


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *