Hi,
I have read a number of documents and done a heap of google searches and things just don't seem to be working for me. I'm trying to configure SSL connections to an Oracle database for testing with a new product. My main problem is the Listener and the SSL they just dont seem to work, I must be missing something basic.
Im trying to set this up under Windows 2012 R2 server in our VM environment. Oracle has been installed under a user called ORASRV that was created by the installer when the software was installed. This is the standard edition of Oracle. The ORASRV user has full rights to the installation directory (have double checked this) of F:\ORACLE
The two websites I read/followed that had example configurations:
https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections
https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/how-to-setting-up-encrypted-communications-channels-in-oracle-database/
All the below commands have been run on the server only.
# create the wallet
orapki wallet create -wallet "f:\oracle\wallet" -pwd <password> -auto_login_local
# generate the self signed certificate with 10 year life space
orapki wallet add -wallet "f:\oracle\wallet" -pwd <password> -dn "CN=AUORASSL01" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
# display the contents of the wallet (made a mistake with the first time run)
orapki wallet display -wallet "f:\oracle\wallet" -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=AUORASSL01
Trusted Certificates:
Subject: CN=AUORASSL01
# The Listener Config
WALLET_LOCATION = (SOURCE = (METHOD = file)(METHOD_DATA =(DIRECTORY = F:\oracle\wallet)))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = AUORASSL01)(PORT = 2484))
(ADDRESS = (PROTOCOL = TCP)(HOST = AUORASSL01)(PORT = 1521))
)
)
# SQLNET.ORA config
WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=F:\oracle\wallet)))
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
# The TNSNAMES.ORA entry for my database
ORASSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = auorassl01)(PORT = 2484))
)
(CONNECT_DATA =
(SERVICE_NAME = orassl)
)
)
LISTENER_ORASSL =
(ADDRESS = (PROTOCOL = TCPS)(HOST = AUORASSL01)(PORT = 2484))
# Restarted the Listener and found these entries in the trace log file
2020-02-25T09:24:31.114683+11:00
System parameter file is f:\oracle\12.2.0\server\network\admin\listener.ora
Log messages written to f:\oracle\diag\tnslsnr\AUORASSL01\listener\alert\log.xml
Trace information written to f:\oracle\diag\tnslsnr\AUORASSL01\listener\trace\ora_2728_2832.trc
Trace level is currently 0
Started with pid=2728
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=AUORASSL01)(PORT=2484)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AUORASSL01)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
2020-02-25T09:24:42.435026+11:00
25-FEB-2020 09:24:42 * 12560
TNS-12560: TNS:protocol adapter error
TNS-00540: SSL protocol adapter failure
# Checked the status of the Listener
F:\oracle>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 25-FEB-2020 09:25:04
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=AUORASSL01)(PORT=2484)))
ORA-28864: SSL connection closed gracefully
TNS-12560: TNS:protocol adapter error
TNS-00542: SSL Handshake failed
64-bit Windows Error: 28864: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AUORASSL01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date 25-FEB-2020 09:24:31
Uptime 0 days 0 hr. 0 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File f:\oracle\12.2.0\server\network\admin\listener.ora
Listener Log File f:\oracle\diag\tnslsnr\AUORASSL01\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=AUORASSL01)(PORT=2484)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AUORASSL01)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orassl" has 1 instance(s).
Instance "orassl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
# running tnsping for the database on the database server
F:\oracle>tnsping orassl
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 25-FEB-2020 09:28:28
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
f:\oracle\12.2.0\server\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = auorassl01)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = orassl)))
TNS-12560: TNS:protocol adapter error
# trying to connect to the database
F:\oracle>sqlplus system@orassl
SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 25 09:29:48 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28864: SSL connection closed gracefully