Oracle Database Network Encryption – SSL/TLS using Wildcard Certificate

Jamsher Khan 21 mins0
()

Oracle Database Network Encryption – SSL/TLS using Wildcard Certificate


Network encryption is one of the most important security strategies in the Oracle database. It will ensure data transmitted over the wire is encrypted and will prevent malicious attacks in man-in-the-middle form. Network encryption guarantees that data exchanged between the Application/End Users and Database System should be securely transmitted and transparently encrypted.

 

Since Oracle 10.2: “Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

 

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)

 

In this blog post, we are going to discuss How to configure Oracle Database connection with SSL/TLS.

 

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


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


Server Side Changes


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@test-machine01 db_wallet]$ pwd
/u01/app/oracle/admin/ucas/db_wallet
[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 5597 Dec  6 17:05 dbcert.pfx
[oracle@test-machine01 db_wallet]$

[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$

[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$

[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$


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

[oracle@test-machine01 12C_wallet]$
[oracle@test-machine01 12C_wallet]$ pwd
/u01/app/oracle/admin/ucas/12C_wallet
[oracle@test-machine01 12C_wallet]$
[oracle@test-machine01 12C_wallet]$ ls -ltr
total 8
-rw-r--r--. 1 oracle oinstall 5597 Dec 24 16:17 dbcert.pfx
[oracle@test-machine01 12C_wallet]$
[oracle@test-machine01 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@test-machine01 12C_wallet]$
[oracle@test-machine01 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@test-machine01 12C_wallet]$
[oracle@test-machine01 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@test-machine01 12C_wallet]$
[oracle@test-machine01 12C_wallet]$
[oracle@test-machine01 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@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$ pwd
/u01/app/oracle/admin/ucas/db_wallet
[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$


Step 3. Configure sqlnet.ora: On the server, add the wallet location entries into the “$ORACLE_HOME/network/admin/sqlnet.ora” file. We will be doing SSL ENCRYPTION AND SERVER AUTHENTICATION. So CLIENT_AUTHENTICATION parameter set to FALSE.

[oracle@test-machine01 admin]$ vi sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/admin/db_wallet)
     )
   )

SSL_CLIENT_AUTHENTICATION = FALSE
:wq!
[oracle@test-machine01 admin]$


Step 4. Configure listener.ora: 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.

[oracle@test-machine01 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = test-machine01)(PORT = 1522))
    )
  )

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

ADR_BASE_LISTENER = /u01/app/oracle
SSL_CLIENT_AUTHENTICATION = FALSE
:wq!
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2021 19:13:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
The command completed successfully
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2021 19:13:57

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

Starting /u01/app/oracle/product/11.2.0.4/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test-machine01)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-DEC-2021 19:13:58
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test-machine01)(PORT=1522)))
The listener supports no services
The command completed successfully
[oracle@test-machine01 admin]$


Step 5. Verify SSL configuration is working fine: 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@test-machine01 db_wallet]$ pwd
/u01/app/oracle/admin/ucas/db_wallet
[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 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@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$ openssl s_client -connect test-machine01: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
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
[oracle@test-machine01 db_wallet]$


Client Side Changes


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.

:\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=test-machine01)(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>


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!

Related Articles