Oracle 12c and SSL Configuration

3818205

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 [email protected]

    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

azielinski

I stumpled across the same problem and was able to solve it and establish a connection via SSL.

When creating the wallet orapki disabled the permission inheritance of the files within the wallet, e.g. cwallet.sso and only allows the user that cerated the wallet to access those files. When allowing the Oracle Listener Service User to read this files and after that restarting the listener service, I was able to connect via SSL.

My SQL.NETORA:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\Zertifikat\wallet)))

SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION = 1.2

SSL_SERVER_DN_MATCH = NO

SQLNET.AUTHENTICATION_SERVICES= (BEQ,TCPS,NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = C:\Oracle\x64\Database\18\log

My Listener.ora:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:/Zertifikat/wallet)))

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION=1.2

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCPS)(HOST = databaseFQDN)(PORT = 1522))

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

    )

  )

ADR_BASE_LISTENER = C:\Oracle\x64\Database\18\log

Test with tnsping:

C:\Users\<User>>tnsping ssl

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 18-JUN-2020 09:37:48

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

Parameterdateien verwendet:

C:\Oracle\TNS_ADMIN\sqlnet.ora

Adapter TNSNAMES zur Aufl÷sung des Alias verwendet

Verbindungsversuch mit (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = databaseFQDN)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SERVICENAME)))

OK (510 ms)

Jaco P.

Maybe this note can help you with the issue:

Step by Step Guide To Configure SSL Authentication (Doc ID 736510.1)

Gaz in Oz

Your listener.ora and sqlnet,ora don't look like the example setup given in Oracle-Base web link.

For instance, the server sqlnet.ora has NTS in the sqlnet.ora, along with a CIPHER list:

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

The listener.ora has

SSL_CLIENT_AUTHENTICATION = FALSE

Once the listener is started, the errors clearly show that SSL has been mis-configured.

The lsnrctl status just confirms it.

It is pointless going further till you fix the listener errors.

3818205

I have re-tried doing the example with 19c and the listener does appear to start without error:

C:\Windows\system32>lsnrctl start

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 06-MAR-2020 14:11:05

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

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production

System parameter file is F:\Software\WINDOWS.X64_193000_db_home\network\admin\listener.ora

Log messages written to F:\oracle\diag\tnslsnr\AUORASSL01\listener\alert\log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AUORASSL01.ldc.com)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=AUORASSL01.ldc.com)(PORT=2484)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AUORASSL01.ldc.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                    LISTENER

Version                  TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production

Start Date                06-MAR-2020 14:11:11

Uptime                    0 days 0 hr. 0 min. 8 sec

Trace Level              off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File  F:\Software\WINDOWS.X64_193000_db_home\network\admin\listener.ora

Listener Log File        F:\oracle\diag\tnslsnr\AUORASSL01\listener\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AUORASSL01.ldc.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=AUORASSL01.ldc.com)(PORT=2484)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

I did make the changes so that they're the sam(ish) between the SQLNET.ORA and LISTENER.ORA file:

LISTENER.ORA:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = F:\Software\WINDOWS.X64_193000_db_home)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:F:\Software\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")

    )

    (SID_DESC =

      (SID_NAME = orassl)

      (ORACLE_HOME = F:\Software\WINDOWS.X64_193000_db_home)

      (SERVICE_NAME = orassl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = AUORASSL01.ldc.com)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCPS)(HOST = AUORASSL01.ldc.com)(PORT = 2484))

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

    )

  )

WALLET_LOCATION = (SOURCE = (METHOD = file)(METHOD_DATA = (DIRECTORY = F:\oracle\wallet)))

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

SQLNET.ORA:

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY = F:\oracle\wallet)))

TNSNAMES.ORA:

ORASSL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCPS)(HOST = AUORASSL01.ldc.com)(PORT = 2484))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orassl)

    )

  )

When I try and connect to the database locally on the server I am still getting the error "ERROR: ORA-28864: SSL connection closed gracefully". I have read through the support document id as suggested by the other reply as well. Is there something specific that I need to do to the database? Of each of the articles I have read through I haven't really seen anything that says if you have to make changes to the DB.

T1DSoldier

@3818205

where you able to get this worked out? I can give you my listener and sqlnet. I use cac to connect but not much different then how you are doing it. Your SSL error is 1 i normally get when I timeout on my select a cert prompt, so a timeout. Also I removed all options from my client sqlnet so it minimizes complication.

Not to go after easy things but just a few things to confirm

Also is your wallet auto-log on?

Are the privs set correctly on the wallet?

my cwallet.sso is 600

my ewallet.p12 is 766

3818205

Hi T1DSoldier, This implementation is being done under windows and permissions have been set to the user that is set running against the oracle services so permissions shouldn't be an issue. Yes I believe I am using auto login as this is the parameter I am using when creating the wallet "-auto_login_local"

If you could post or send me your listener/sqlnet files that would be great so I can compare against mine. I strongly suspect that there is something basic that i'm not picking up on.

T1DSoldier

so I have never user the auto_login_local but i assume its not an issue. Also I know for cac smart card login enabling cipher suites breaks the connection. What error are you getting?

so moving to 19c I have done some testing to simplify troubleshooting. I did have everything working on 12c before moving to 19c. When moving to 19c I was able to remove the 2nd sqlnet because of a new sqlnet parameter SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION= 1.1 --i am not sure how the self signed cert works with this parameter but you may have to play with this setting. Also parameters in the sqlnet seem to take precedence so if you have SSL_VERSION=1.1 it will use 1,1

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/product/19.0.0/dbhome_1/admin/orcl/wallet)

    )

  )

--I removed the sid list, that will not have any affect on your connection. I think i like the dynamic listener

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = i uses ip)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCPS)(HOST = i use ip(PORT = 2484))

    )

   )

---sqlnet

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools., EZCONNECT

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/product/19.0.0/dbhome_1/admin/orcl/wallet)

    )

  )

#/u01/app/oracle/product/19.0.0/dbhome_1/admin/orcl/wallet

#fixes double encryption

SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE

SQLNET.AUTHENTICATION_SERVICES= (TCPS,BEQ,IPC)

#sql client version allowed no affect on encryption

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=12

SSL_VERSION =  1.1

SSL_CLIENT_AUTHENTICATION = TRUE

#ENCRYPTION_TYPES

SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256)

#prior to 19c this would fail with cac this setting fixes it SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS

SQLNET.ENCRYPTION_CLIENT = REQUIRED

SQLNET.ENCRYPTION_SERVER = REQUIRED

#CHECKSUM_TYPES

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA384)

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA384)

SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED

#CIPHER_SUITES

#SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_256_GCM_SHA384)

testing errors

#SSL_RSA_WITH_AES_256_CBC_SHA384 (fatalerror), SSL_RSA_WITH_AES_128_CBC_SHA256 (fatalerror), SSL_RSA_WITH_AES_128_GCM_SHA256(fatalerror)

trace_directory_server=/home/oracle/Documents/trc

trace_file_server=ser_12_trace_new.trc

TRACE_LEVEL_server=16

TRACE_UNIQUE_server=ON

TRACE_TIMESTAMP_server=ON

#DIAG_ADR_ENABLED=OFF

ADR_BASE = /u01/app/oracle

make sure you have the trusted chain on both client and server

drwxr-xr-x. 2 oracle oinstall  158 Mar 31 13:07 backup

-rw-------. 1 oracle oinstall 5941 Mar 31 16:26 cwallet.sso

-rwxrw-rw-. 1 oracle oinstall    0 Mar 31 12:06 cwallet.sso.lck

-rwxrw-rw-. 1 oracle oinstall 5896 Mar 31 16:26 ewallet.p12

-rwxrw-rw-. 1 oracle oinstall 2400 Mar 31 12:09 ewallet.p12.bck

-rwxrw-rw-. 1 oracle oinstall    0 Mar 31 12:08 ewallet.p12.lck

pmdba

These guides were written for CAC configuration, but as @T1DSoldier noted there shouldn't be much difference, especially in the .ora file settings. See if this helps:

https://pmdba.files.wordpress.com/2019/03/configuring_ssl_for_oracle_client_with_cac_using_mcs_4.pdf

3818205

Thanks @pmdba for the link. This has helped me a bit as well as simplifying some items of the config. At this point I have gotten the SSL listener port up and the listener running without errors now. I can also run tnsping to the database on the system local to the database as well as sqlplus connection to the database.

I am still having some issues running tnsping to the database. I'm getting this error now:

    20-MAY-2020 10:52:26 * (ADDRESS=(PROTOCOL=tcps)(HOST=10.105.10.151)(PORT=64654)) * <unknown connect ata> * 542

    TNS-00542: SSL Handshake failed

     TNS-12560: TNS:protocol adapter error

T1DSoldier

Are you saying you can connect to the instance with a normal user through the listener or are you saying you can connect to the instance locally sqlplus /?

That error you are showing is also in our listener log. I have yet tracked it down but nothing we are using is broken so I never really dedicated much time to find it.

</msg>

<msg time='2020-05-20T04:30:48.236-04:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='xxxxxxxxx'

host_addr='xxxxxxxxxx' pid='25738'>

<txt>20-MAY-2020 04:30:48 * (ADDRESS=(PROTOCOL=tcps)(HOST=xxxxxxxxxxxx)(PORT=63981)) * &lt;unknown connect data&gt; * 542

</txt>

</msg>

<msg time='2020-05-20T04:30:48.237-04:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='xxxxxxxxxxx'

host_addr='xxxxxxxxxx' pid='25738'>

<txt>TNS-00542: SSL Handshake failed

TNS-12560: TNS:protocol adapter error

Now on our test set up that runs 1 Dynamic Listener listening port 1521 and 2484 i no longer get those errors

3818205

Ok so I thought I would post my listener config to show how simple it became:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = F:\Software\WINDOWS.X64_193000_db_home)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:F:\Software\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")

    )

    (SID_DESC =

      (SID_NAME = orassl)

      (ORACLE_HOME = F:\Software\WINDOWS.X64_193000_db_home)

      (SERVICE_NAME = orassl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = AUORASSL01.ldc.com)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCPS)(HOST = AUORASSL01.ldc.com)(PORT = 2484))

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

    )

  )

WALLET_LOCATION =

  (SOURCE =

    (METHOD = file)   

    (METHOD_DATA =

      (DIRECTORY = F:\oracle\wallet)

  )

)

SSL_VERSION=undetermined

this has allowed tnsping and sqlplus local to the database to work as expected and when I login in on the system running the database is successfully shows the connection as tcps:

C:\Windows\system32>sqlplus sys/<password>@orassl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 19 15:40:56 2020

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SELECT SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

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

tcps

So at this point everything looks okay. But now as I start testing connections external to the database i am now running into TNS protocol errors:

C:\Users\user>tnsping orassl

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 27-MAY-2020 14:57:16

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:

c:\oracle\18.3.0\client_64\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = auorassl01.ldc.com)(PORT = 2484))) (CONNECT_DATA = (SID = ORASSL) (SERVER = DEDICATED) (SECURITY = (SSL_SERVER_CERT_DN = CN=AUORASSL01.ldc.com,OU=company,O=company))))

TNS-12560: TNS:protocol adapter error

Currently this is my tnsnames.ora entry for my database:

ORASSL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCPS)(HOST = auorassl01.ldc.com)(PORT = 2484))

    )

    (CONNECT_DATA =

      (SID = ORASSL)

      (SERVER = DEDICATED)

      (SECURITY = (SSL_SERVER_CERT_DN = "CN=AUORASSL01.ldc.com,OU=company,O=company"))

    )

  )

I have tried both with and without the security parameter to no success.

This is the SQLNET.ORA from my client machien:

NAMES.DIRECTORY_PATH =(TNSNAMES, EZCONNECT)

SQLNET.AUTHENTICATION_SERVICES = (NONE,TCPS,BEQ)

SSL_SERVER_DN_MATCH = TRUE

SSL_VERSION=undetermined

WALLET_LOCATION =

  (SOURCE =

    (METHOD = file)

    (METHOD_DATA =

      (DIRECTORY = c:\oracle\wallet)

    )

  )

T1DSoldier

So you will want to trace the connect, i was able to get a tnsname error just by clicking cancel when it asked me to pick a cert so it could be something as simple as privs being wrong on your cert

H:\>tnsping oratest_2484

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 27-MAY-2020 08:48:20

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:

C:\OracleConfig\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcps)(PORT = 2484)(HOST = xxxxxxxxx)) (CONNECT_DATA = (SID = orcltest)))

TNS-12560: TNS:protocol adapter error

azielinski

I stumpled across the same problem and was able to solve it and establish a connection via SSL.

When creating the wallet orapki disabled the permission inheritance of the files within the wallet, e.g. cwallet.sso and only allows the user that cerated the wallet to access those files. When allowing the Oracle Listener Service User to read this files and after that restarting the listener service, I was able to connect via SSL.

My SQL.NETORA:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\Zertifikat\wallet)))

SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION = 1.2

SSL_SERVER_DN_MATCH = NO

SQLNET.AUTHENTICATION_SERVICES= (BEQ,TCPS,NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = C:\Oracle\x64\Database\18\log

My Listener.ora:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:/Zertifikat/wallet)))

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION=1.2

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCPS)(HOST = databaseFQDN)(PORT = 1522))

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

    )

  )

ADR_BASE_LISTENER = C:\Oracle\x64\Database\18\log

Test with tnsping:

C:\Users\<User>>tnsping ssl

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 18-JUN-2020 09:37:48

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

Parameterdateien verwendet:

C:\Oracle\TNS_ADMIN\sqlnet.ora

Adapter TNSNAMES zur Aufl÷sung des Alias verwendet

Verbindungsversuch mit (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = databaseFQDN)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SERVICENAME)))

OK (510 ms)

3818205

OMG....that was it. The security setup of the local windows user that is created during the installation of Oracle. When I checked the permissions of my wallets that have been created they were all set to onlt allow the local Administrator to access the files. So it appears that the utilities under windows do not take into account whether the software is installed under its own user or if it uses the standard windows service accounts.

I made the same change so that the oracle user (oraclient) on my laptop can also access the files, other than my own account and bingo tnsping works and sqlplus worked.

I think when I did get it working locally, I forgot to make notes about the changes I made to the windows oracle user and giving it permissions to access the files. I have made notes of this now very prominently.