How to enable SSL encryption for Oracle SQL*Net (Without SSH Authentication)

1) Create a directory to store all our wallets:

oracle@solaris:~$ mkdir /u01/app/oracle/wallet

oracle@solaris112:~$ mkdir /u01/app/oracle/wallet

2) Create a wallet for the primary database. Create an empty wallet with auto login enabled:

oracle@solaris:~$ cd /u01/app/oracle/wallet

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris:/u01/app/oracle/wallet$ ls -l /u01/app/oracle/wallet

total 6

-rw------- 1 oracle oinstall 120 Dec 14 23:29 cwallet.sso

-rw-rw-rw- 1 oracle oinstall 0 Dec 14 23:29 cwallet.sso.lck

-rw------- 1 oracle oinstall 75 Dec 14 23:29 ewallet.p12

-rw-rw-rw- 1 oracle oinstall 0 Dec 14 23:29 ewallet.p12.lck

oracle@solaris112:/u01/app/oracle/wallet$ orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris112:/u01/app/oracle/wallet$ ls -l /u01/app/oracle/wallet

total 4

-rw------- 1 oracle oinstall 120 Dec 15 07:30 cwallet.sso

-rw-rw-rw- 1 oracle oinstall 0 Dec 15 07:30 cwallet.sso.lck

-rw------- 1 oracle oinstall 75 Dec 15 07:30 ewallet.p12

-rw-rw-rw- 1 oracle oinstall 0 Dec 15 07:30 ewallet.p12.lck

3) Add a self-signed certificate in the wallet (a new pair of private/public keys is created):

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -dn "CN=solaris.dbaglobe.com" -keysize 1024 -self_signed -validity 365 -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris112:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -dn "CN=solaris112.dbaglobe.com" -keysize 1024 -self_signed -validity 365 -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

If you display the server’s wallet you will see the following requested certificate:

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet display -wallet /u01/app/oracle/wallet Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Subject: CN=solaris.dbaglobe.com

Trusted Certificates:

Subject: CN=solaris.dbaglobe.com

oracle@solaris112:~$ orapki wallet display -wallet /u01/app/oracle/wallet Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Subject: CN=solaris112.dbaglobe.com

Trusted Certificates:

Subject: CN=solaris112.dbaglobe.com

4) Export the certificate:

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet export -wallet /u01/app/oracle/wallet -dn "CN=solaris.dbaglobe.com" -cert /u01/app/oracle/wallet/solaris.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris112:~$ orapki wallet export -wallet /u01/app/oracle/wallet -dn "CN=solaris112.dbaglobe.com" -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

5) Exchange the server and client their public keys:

oracle@solaris:/u01/app/oracle/wallet$ scp solaris112:/u01/app/oracle/wallet/solaris112.dbaglobe.com.txt .

oracle@solaris:/u01/app/oracle/wallet$ scp /u01/app/oracle/wallet/solaris.dbaglobe.com.txt solaris112:/u01/app/oracle/wallet/

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris112:~$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris.dbaglobe.com.txt -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet display -wallet /u01/app/oracle/wallet Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Subject: CN=solaris.dbaglobe.com

Trusted Certificates:

Subject: CN=solaris112.dbaglobe.com

Subject: CN=solaris.dbaglobe.com

oracle@solaris112:~$ orapki wallet display -wallet /u01/app/oracle/wallet

Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Subject: CN=solaris112.dbaglobe.com

Trusted Certificates:

Subject: CN=solaris.dbaglobe.com

Subject: CN=solaris112.dbaglobe.com

6) Configure the listeners to work with TCPS

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl stop

Edit server side configuration use netmgr. Sample configuration as below:

oracle@solaris:/u01/app/oracle/wallet$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/wallet)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

oracle@solaris:/u01/app/oracle/wallet$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

SSL_VERSION = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/wallet)

)

ADR_BASE = /u01/app/oracle

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl start

7) Configure the sqlnet.ora & tnsnames.ora on the client

oracle@solaris112:~$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

#Parameter for TDE

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/wallet)

)

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

SSL_VERSION = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/wallet)

)

ADR_BASE = /u01/app/oracle

oracle@solaris112:~$ echo "

> ORCL1_TCPS =

> (DESCRIPTION =

> (ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))

> (CONNECT_DATA =

> (SERVER = DEDICATED)

> (SERVICE_NAME = orcl1)

> )

> " >> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

8) Check the connectivity

oracle@solaris112:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin$ tnsping ORCL1_TCPS

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 08:39:03

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

OK (20 msec)

oracle@solaris112:~$ sqlplus system/password@orcl1_tcps

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 08:39:57 2014

Last Successful login time: Mon Dec 15 2014 00:39:21 +08: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> select sys_context('userenv','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

Tcps

SQL> select userenv('sid') from dual;

USERENV('SID')

--------------

SQL> select * from v$session_connect_info where sid=50;

SID SERIAL# AUTHENTICATION_TYPE OSUSER

---------- ---------- -------------------------- ------------------------------

NETWORK_SERVICE_BANNER

--------------------------------------------------------------------------------

CLIENT_CHARSET CLIENT_CONNEC

---------------------------------------- -------------

CLIENT_OCI_LIBRARY CLIENT_VERSION

--------------------------- ----------------------------------------

CLIENT_DRIVER CLIENT_LOBATTR CLIENT_REGID CON_ID

------------------------------ ----------------------- ------------ ----------

50 14986 DATABASE oracle

US7ASCII Homogeneous

Home-based 12.1.0.2.0

SQL*PLUS Client Temp Lob Rfc On 0 0

50 14986 DATABASE oracle

Authentication service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII Homogeneous

Home-based 12.1.0.2.0

SQL*PLUS Client Temp Lob Rfc On 0 0

50 14986 DATABASE oracle

Encryption service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII Homogeneous

Home-based 12.1.0.2.0

SQL*PLUS Client Temp Lob Rfc On 0 0

50 14986 DATABASE oracle

Crypto-checksumming service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII Homogeneous

Home-based 12.1.0.2.0

SQL*PLUS Client Temp Lob Rfc On 0 0

Possible errors 1: (To resolve it, include –pwd parameter)

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

Could not install trusted cert at/u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

PKI-02008: Unable to modify a read-only Auto-login wallet.

Possible errors 2: (To resolve it, use different port number for TCP and TCPS)

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl start

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 00:02:21

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production

System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/solaris/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris)(PORT=1521)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=solaris)(PORT=1521)))

TNS-12542: TNS:address already in use

TNS-12560: TNS:protocol adapter error

TNS-00512: Address already in use

Solaris Error: 125: Address already in use

Listener failed to start. See the error message(s) above...

Possible errors 3: (To resolve it, use proper wallet location)

oracle@solaris112:~$ sqlplus system/p_ssw0rd@orcl1_tcps

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 08:14:21 2014

ERROR:

ORA-28759: failure to open file

oracle@solaris112:~$ tnsping orcl1_tcps

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 08:14:50

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

TNS-12560: TNS:protocol adapter error

4 comments:

UnknownMarch 26, 2019 at 9:56 PM

The following does not set up the alias corredctly for SSL.

ADR_BASE = /u01/app/oracle

oracle@solaris112:~$ echo "

> ORCL1_TCPS =

> (DESCRIPTION =

> (ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))

> (CONNECT_DATA =

> (SERVER = DEDICATED)

> (SERVICE_NAME = orcl1)

> )

> )

>

> " >> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
Saqib KhatriNovember 8, 2019 at 11:02 PM
Thanks for picking out the time to discuss this, I feel great about it and love studying more on this topic. It is extremely helpful for me. Thanks for such a valuable help again. RapidSSL Wildcard
ElezebethSeptember 28, 2020 at 4:28 PM
Once you have a percentage of the online currency, you may now utilize it to buy anything that acknowledges it. Now and again, Bitcoin is the main type of installment, and you will need to procure it to successfully complete an online transaction. bitcoin mixer
MH_ServicesMarch 9, 2022 at 11:34 PM
sale SSL Certificates are a vital component to protect the owners of e-commerce websites as well as their customers. The primary function of an SSL Certificate is to encrypt confidential information such as credit card numbers, addresses, social security numbers and the like.

Donghua's Blog - DBAGlobe

Monday, December 15, 2014