Configure SSL and TLS connection in Oracle Database

SSL OR TSL is the method used for Oracle Advanced Security to secure the connection between Client or Server.

Note: SSL was developed by Netscape Communications Corporation, the Internet Engineering Task Force (IETF) took over SSL and renamed it Transport Layer Security (TLS).

Oracle Advanced Security feature

Authentication process with help of SSL or TSL as follows in Oracle:
1. On a client, the user makes a connection to the server by using SSL.
2. SSL verified the connection between the client and the server.
3. If connection is successful, the db server check the user has the authorization to access the database.
4. Then connection is established.

Install Oracle Advanced Security on both the client and server

Create and Configure the Wallet on Server

1. Create the Wallet directory on Operating system.

mkdir wallet_location


Example
c:\oracle>mkdir wallet


2. Go to the directory location.

cd wallet_location


Example
cd C:\oracle\wallet


3. Run orapki command to create the initial wallet.

orapki wallet create -wallet wallet_location -auto_login -pwd


Example
C:\Oracle\wallet>orapki wallet create -wallet C:\oracle\wallet -auto_login -pwd
sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


4. It will create two files in the directory.

$dir
cwallet.sso
ewallet.p12


Example
C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:47 .
17-05-2018 07:47 ..
17-05-2018 07:47 2,901 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:47 2,856 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
4 File(s) 5,757 bytes
2 Dir(s) 27,854,073,856 bytes free


5. Create a self-signed certificate.
Note: Command will generate both a user certificate and the CA root certificate.

orapki wallet add -wallet wallet_location -dn "CN=server" -keysize 512 -self_signed -validity 365 -pwd

Example
C:\Oracle\wallet>orapki wallet add -wallet C:\Oracle\wallet -dn "CN=server" -key
size 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:47 .
17-05-2018 07:47 ..
17-05-2018 07:48 4,405 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:48 4,360 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
4 File(s) 8,765 bytes
2 Dir(s) 27,857,510,400 bytes free


6. Export the CA Certificate.

orapki wallet export -wallet wallet_location -dn "CN=server" -cert server_ca.cert

Example
C:\Oracle\wallet>orapki wallet export -wallet C:\Oracle\wallet -dn "CN=server" -cert server_ca.cert
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:49 .
17-05-2018 07:49 ..
17-05-2018 07:48 4,405 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:48 4,360 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
17-05-2018 07:49 427 server_ca.cert
5 File(s) 9,192 bytes
2 Dir(s) 27,857,936,384 bytes free


Create and Configure the wallet on Client

1. Create the Wallet directory on Operating system.

mkdir wallet_location


Example
C:\oraclient>mkdir wallet


2. Go to the directory location.

cd wallet_location


Example
C:\oraclient>cd wallet
C:\oraclient\wallet>


3. Run orapki command to create the initial wallet.

orapki wallet create -wallet wallet_location -auto_login -pwd


Example:
C:\oraclient\wallet>orapki wallet create -wallet C:\oraclient\wallet -auto_login -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


4. It will create two files in the directory.

$dir
cwallet.sso
ewallet.p12


Example
C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:42 .
16-05-2018 21:42 ..
16-05-2018 21:42 2,901 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:42 2,856 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
4 File(s) 5,757 bytes
2 Dir(s) 27,907,039,232 bytes free


5. Create a self-signed certificate.
Note: Command will generate both a user certificate and the CA root certificate.

orapki wallet add -wallet wallet_location -dn "CN=client" -keysize 512 -self_signed -validity 365 -pwd

Example:
C:\oraclient\wallet>orapki wallet add -wallet C:\oraclient\wallet -dn "CN=client
" -keysize 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:42 .
16-05-2018 21:42 ..
16-05-2018 21:45 4,397 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:45 4,352 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
4 File(s) 8,749 bytes
2 Dir(s) 27,884,232,704 bytes free


6. Export the CA Certificate.

orapki wallet export -wallet wallet_location -dn "CN=client" -cert client_ca.cert

Example
C:\oraclient\wallet>orapki wallet export -wallet C:\oraclient\wallet -dn "CN=cli
ent" -cert client_ca.cert
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:47 .
16-05-2018 21:47 ..
16-05-2018 21:47 427 client_ca.cert
16-05-2018 21:45 4,397 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:45 4,352 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
16-05-2018 21:47 0 wallet_location.lck
6 File(s) 9,176 bytes
2 Dir(s) 27,882,811,392 bytes free


Final Steps to Exchange the certificate in between Client and Server

1. Transfer the exported Certificate from Server to client and Client to Server with FTP.

2. Import the Server Certificate to client system.

orapki wallet add -wallet wallet_location -trusted_cert -cert server_ca.cert -pwd

Example
C:\oraclient\wallet>orapki wallet add -wallet C:\oraclient\wallet -trusted_cert -cert server_ca.cert -pwd sys123456
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.


C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
17-05-2018 07:50 .
17-05-2018 07:50 ..
16-05-2018 21:56 427 client_ca.cert
17-05-2018 07:51 5,445 cwallet.sso
17-05-2018 07:51 5,368 ewallet.p12
17-05-2018 07:49 427 server_ca.cert
4 File(s) 11,667 bytes
2 Dir(s) 28,018,552,832 bytes free


3. Import the Client Certificate to Server system.

orapki wallet add -wallet wallet_location -trusted_cert -cert client_ca.cert -pwd

C:\Oracle\wallet>orapki wallet add -wallet C:\oracle\wallet -trusted_cert -cert
client_ca.cert -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.


C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:50 .
17-05-2018 07:50 ..
16-05-2018 21:56 427 client_ca.cert
17-05-2018 07:51 4,765 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:51 4,720 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
17-05-2018 07:49 427 server_ca.cert
6 File(s) 10,339 bytes
2 Dir(s) 28,019,503,104 bytes free


Configure the listener to TCPS protocol

1. Modify the Listener to work on TCPS protocol on Server side and restart it.
Add wallet location entry and address with TCPS protocol with port different then already used in Listener.ora file.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = XXXX))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XXXX)(PORT = XXXX))
)
)


WALLET_LOCATION =
(SOURCE=
(METHOD=File)
(METHOD_DATA=
(DIRECTORY=wallet_location)))


2. Modify the SQLNET.ORA file in Server side
Note: If you want to support both SSL_VERSION(TSL version) then use SQLNET entry with OR operator as shown: SSL_VERSION=1.2 or 1.1 otherwise use separate mention one version which you want to use SSL_VERSION=1.2

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = wallet_location)
)
)


3. Modify the SQLNET.ORA file in CLIENT Side.
Note: If you want to support both SSL_VERSION(TSL version) then use SQLNET entry with OR operator as shown: SSL_VERSION=1.2 or 1.1 otherwise use separate mention one version which you want to use SSL_VERSION=1.2

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE


WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = wallet_location)
)
)


4. Modify the TNSNAMES.ORA file and make entry using TCPS protocol for connectivity at CLIENT side.

ORCLTCPS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XXXX)(PORT = XXXX))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)


Configuring the database
1. Database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.

alter system set remote_os_authent=FALSE scope=spfile;
alter system set os_authent_prefix='' scope=spfile;


2. Restart the database

shutdown immediate;
startup;


3. User within the database has to be created specifying the distiguished name (DN) on their certificate and have create session grant.

create user client identified externally as 'CN=client';
grant create session to client;


Testing the SSL or TSL connection is working
1. Tnsping the ORCL entry make in CLIENT Side.
tnsping ORCLTCPS

2. Connect with SQLPLUS with tns entry and checked user or PROTOCOL.

sqlplus client@ORCLTCPS
SQL> select user from dual;
USER
------------------------------
CLIENT


select sys_context('userenv','network_protocol') from dual;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps


Error during configure wallet
Error
C:\Oracle\wallet>orapki wallet add -wallet wallet_location -dn “CN=server” -keysize 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
PKI-02003: Unable to load the wallet at: wallet_location

Solution
Specify the correct wallet location which exist physically in Operating system.

Error
C:\oraclient\wallet>orapki wallet create -wallet C:\oraclient\wallet -auto_login -pwd sys123
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
PKI-01002: Invalid password:Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters.

Solution
Use the password with eight characters at least.