Forum Stats

  • 3,726,656 Users
  • 2,245,235 Discussions
  • 7,852,337 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Oracle 12c and SSL Configuration

3818205
3818205 Member Posts: 6

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

Tagged:
Dejan T.T1DSoldierCarstenL91user8601919

Best Answer

  • azielinski
    azielinski Member Posts: 1 Red Ribbon
    edited June 2020 Accepted Answer

    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)

    T1DSoldierCarstenL91

Answers

  • Jaco P.
    Jaco P. Member Posts: 47 Blue Ribbon
    edited February 2020

    Maybe this note can help you with the issue:

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

    Dejan T.
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,773 Bronze Crown
    edited February 2020

    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
    3818205 Member Posts: 6
    edited March 2020

    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
    T1DSoldier Member Posts: 55 Red Ribbon
    edited May 2020

    @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
    3818205 Member Posts: 6
    edited May 2020

    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
    T1DSoldier Member Posts: 55 Red Ribbon
    edited May 2020

    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
    pmdba Member Posts: 103 Bronze Badge
    edited May 2020

    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
    3818205 Member Posts: 6
    edited May 2020

    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
    T1DSoldier Member Posts: 55 Red Ribbon
    edited May 2020

    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
    3818205 Member Posts: 6
    edited May 2020

    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
    T1DSoldier Member Posts: 55 Red Ribbon
    edited May 2020

    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
    azielinski Member Posts: 1 Red Ribbon
    edited June 2020 Accepted Answer

    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)

    T1DSoldierCarstenL91
  • 3818205
    3818205 Member Posts: 6
    edited June 2020

    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.

    user8601919
Sign In or Register to comment.