CODEWITHSUNDEEP
oracle-databasesslsqlplus
buckc
buckc

Reputation: 61

How can I create a ssl/tls connection from windows client to another windows machine with oracle db 19c?

Goal: Im trying to establish a ssl connection to a oracle 19c database for the purpose of capturing the network traffic generated for another project, but I have had a heck of a time. Any help will be much appreciated. Thanks in advance.

Symptoms: tcp 3 way handshake is started successfully SYN, SYN ACK but the listener immediately shuts it down with a FIN ACK graceful connection.

TNS-12560: TNS:protocol adapter error

TNS-00540: SSL protocol adapter failure

I've used sqlplus and toad.

The non ssl connection to port 1521 works just fine on sqlplus and toad.

listener.ora (SERVER)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\App\db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\App\db_home\bin\oraclr19.dll")
    )
  )

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\App\db_home\wallet)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-10-ORACL-DB)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = WIN-10-ORACL-DB)(PORT = 2484))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2484))
    )
  )

ADR_BASE_LISTENER = C:\App\db_home\log

sqlnet.ora (SERVER)

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)

SSL_VERSION = 3.1

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = FALSE

SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\App\db_home\wallet)
    )
  )

SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_CBC_SHA256, SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_AES_256_GCM_SHA384)

ADR_BASE = C:\App\db_home\log

tnsnames.ora (SERVER)

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1521))
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC2484))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-10-ORACL-DB)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-10-ORACL-DB)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = WIN-10-ORACL-DB)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.greenbuff.local)
    )
  )

sqlnet.ora (CLIENT)

SQLNET.AUTHENTICATION_SERVICE = (TCPS, NTS)

SSL_VERSION= 3.1


SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
    (SOURCE = 
        (METHOD = FILE)
        (METHOD_DATA = 
            (DIRECTORY = C:\oracle\instant_client_19_6\network\wallet)
        )
    )

ADR_BASE = C:\oracle\instant_client_19_6\network\log
NAMES.DIRECTORY_PATH=(EZCONNECT,TNSNAMES)
LOG_DIRECTORY_CLIENT=C:\oracle\instantclient_19_6\network\log
LOG_FILE_CLIENT=sqlnet_log
SQLNET.EXPIRE_TIME=3
TRACE_FILELEN_CLIENT=100
TRACE_FILENO_CLIENT=3
TRACE_LEVEL_CLIENT=support
TRACE_TIMESTAMP_CLIENT=ON
TRACE_UNIQUE_CLIENT=ON
USE_DEDICATED_SERVER=OFF

tnsnames.ora (CLIENT)

REMOTE_SSL_DB = 
(DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 172.20.191.102)(PORT = 2484))
    (CONNECT_DATA=
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcl.greenbuff.local)
    )
)

REMOTE_DB = 
(DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.191.102)(PORT = 1521))
    (CONNECT_DATA=
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcl.greenbuff.local)
    )
)

Upvotes: 1

Views: 5704

Answers (3)

samm
samm

Reputation: 722

I had the same problem on Windows. Referring to pmdba's comment, the root cause is the listener account hadn't read privileges on the wallet files because I saw Application Data, i.e. SSL handshake done, in WireShark. After adding full permissions, it worked for me.

Updated: add official references: About Setting Permissions for Oracle Wallets and About Setting File System ACLs Manually

As Oracle Database services now run under a standard Windows User Account, a file might not be accessible by Oracle Database services unless the file system Access Control Lists (ACLs) grant access to the file.

Though Oracle installation configures the ACLs in a way to ensure that you do not have to change ACLs manually for typical usage, it is necessary to change ACLs manually, for example, to manually upgrade databases, and database files not in Oracle base, or to grant access to wallets in the file system.

The rules to set file system ACLs manually are:

  • To allow Oracle Database service access to a file: Grant access to Oracle Home User for the file when a Windows User Account is used as the Oracle Home User. If a Windows built-in account is used as the Oracle Home User, then no such permission is necessary because the Oracle Database services run under the administrative account.

  • To allow Oracle Grid Listeners services access to a file: Grant access to ORA_GRID_LISTENERS group for the file.

  • To allow Oracle services from a client ORACLE_HOME access to a file: Grant access to Oracle Home User for the file when a Windows User Account is used as the Oracle Home User for the client home. If a Windows built-in account is used as the Oracle Home User, then grant access to the ORA_HOMENAME_SVCSIDS group for the file.

DBeaver complained (a client error example):

javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify
    at java.base/sun.security.ssl.Alert.createSSLException(Unknown Source)
    at java.base/sun.security.ssl.Alert.createSSLException(Unknown Source)
    at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
    at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
    at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
    at java.base/sun.security.ssl.SSLEngineImpl.closeInbound(Unknown Source)
    at oracle.net.nt.SSLSocketChannel.fill(SSLSocketChannel.java:534)
    at oracle.net.nt.SSLSocketChannel.read(SSLSocketChannel.java:161)
    at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
    at oracle.net.ns.NIOPacket.readNIOPacket(NIOPacket.java:252)
    at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:118)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.lambda$0(JDBCDataSource.java:184)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.openConnection(JDBCDataSource.java:203)
    at org.jkiss.dbeaver.ext.oracle.model.OracleDataSource.openConnection(OracleDataSource.java:168)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCExecutionContext.connect(JDBCExecutionContext.java:103)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCRemoteInstance.initializeMainContext(JDBCRemoteInstance.java:100)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCRemoteInstance.<init>(JDBCRemoteInstance.java:59)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.initializeRemoteInstance(JDBCDataSource.java:111)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.<init>(JDBCDataSource.java:99)
    at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.<init>(JDBCDataSource.java:91)
    at org.jkiss.dbeaver.ext.oracle.model.OracleDataSource.<init>(OracleDataSource.java:86)
    at org.jkiss.dbeaver.ext.oracle.OracleDataSourceProvider.openDataSource(OracleDataSourceProvider.java:147)
    at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect(DataSourceDescriptor.java:898)
    at org.jkiss.dbeaver.runtime.jobs.ConnectJob.run(ConnectJob.java:70)
    at org.jkiss.dbeaver.runtime.jobs.ConnectJob.runSync(ConnectJob.java:98)
    at org.jkiss.dbeaver.ui.actions.datasource.DataSourceHandler.connectToDataSource(DataSourceHandler.java:113)
    at org.jkiss.dbeaver.ui.actions.datasource.UIServiceConnectionsImpl.initConnection(UIServiceConnectionsImpl.java:70)
    at org.jkiss.dbeaver.model.navigator.DBNDataSource.initializeNode(DBNDataSource.java:158)
    at org.jkiss.dbeaver.model.navigator.DBNDatabaseNode.getChildren(DBNDatabaseNode.java:225)
    at org.jkiss.dbeaver.model.navigator.DBNDatabaseNode.getChildren(DBNDatabaseNode.java:1)
    at org.jkiss.dbeaver.model.navigator.DBNUtils.getNodeChildrenFiltered(DBNUtils.java:78)
    at org.jkiss.dbeaver.ui.navigator.database.load.TreeLoadService.evaluate(TreeLoadService.java:49)
    at org.jkiss.dbeaver.ui.navigator.database.load.TreeLoadService.evaluate(TreeLoadService.java:1)
    at org.jkiss.dbeaver.ui.LoadingJob.run(LoadingJob.java:88)
    at org.jkiss.dbeaver.ui.LoadingJob.run(LoadingJob.java:72)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Security Permission Process Properties - Security in procexp64

Add permissions in Security and Advance

icacls Displays or modifies discretionary access control lists (DACLs) on specified files, and applies stored DACLs to files in specified directories.

C:\Programs\admin\wallet> icacls .
. BUILTIN\Administrators:(I)(OI)(CI)(F)
  NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F)
  FANG-PC\ORA_OraDB19Home1_SVCACCTS:(I)(OI)(CI)(F)
  FANG-PC\joy:(I)(OI)(CI)(F)

Successfully processed 1 files; Failed processing 0 files

C:\Programs\admin\wallet> icacls ./*
cwallet.sso BUILTIN\Administrators:(R,W,D,WDAC)
            Everyone:(F)

cwallet.sso.lck BUILTIN\Administrators:(R,W,D,WDAC)
                Everyone:(F)

ewallet.p12 BUILTIN\Administrators:(R,W,D,WDAC)
            Everyone:(F)

ewallet.p12.lck BUILTIN\Administrators:(R,W,D,WDAC)
                Everyone:(F)

Successfully processed 4 files; Failed processing 0 files

Note Everyone:(F) for all files were added by me later.

C:\Programs\admin>lsnrctl status LISTENER_TCP_TCPS

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 29-NOV-2021 22:56:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fang-pc)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TCP_TCPS
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                29-NOV-2021 22:37:23
Uptime                    0 days 0 hr. 19 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Programs\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Programs\WINDOWS.X64_193000_db_home\log\diag\tnslsnr\fang-pc\listener_tcp_tcps\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fang-pc)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=fang-pc)(PORT=2484)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=fang-pc)(PORT=5500))(Security=(my_wallet_directory=C:\PROGRAMS\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "52448234712340b69f274bcc790ecfe0" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "7ff434d4927c40dcaaf7eeb756b1d39d" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Programs\admin> netstat -ano | findstr "PID LISTENING" | findstr "PID :1521 :2484"
  Proto  Local Address          Foreign Address        State           PID
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       11160
  TCP    0.0.0.0:2484           0.0.0.0:0              LISTENING       11160
  TCP    [::]:1521              [::]:0                 LISTENING       11160
  TCP    [::]:2484              [::]:0                 LISTENING       11160

:: taskkill /f /fi "SERVICES eq <service_short_name>"

C:\Programs\admin> tasklist /fi "PID eq 11160"

Image Name                     PID Session Name        Session#    Mem Usage
========================= ======== ================ =========== ============
tnslsnr.exe                  11160 Services                   0     24,432 K

C:\Programs\admin> wmic service where "ProcessID=11160" get CreationClassName,Name
CreationClassName  Name
Win32_Service      OracleOraDB19Home1TNSListenerLISTENER_TCP_TCPS

In addition, note orapki wallet export the certificate from ewallet.p12 if it doesn't just contain CN in DN. I found client just received CN=orcl instead of CN=ORCL,O=Company0,C=US (O=Company0,C=US was missing).

C:\Programs\admin> orapki wallet export -wallet "C:\Programs\admin\wallet" -pwd xxx_password -dn "CN=ORCL" -cert tmp.crt
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Please check DN, could not found certificate with matching DN.

C:\Programs\admin> orapki wallet display -wallet C:\Programs\admin\wallet -pwd xxx_password
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=ORCL,O=Company0,C=US
Trusted Certificates:
Subject:        CN=ORCL,O=Company0,C=US


C:\Programs\admin> openssl s_client -showcerts -connect 10.23.56.58:2484 2>NUL <NUL | openssl x509 > Desktop/orcl.crt

Java

public class Test {
    public static void main(String[] args) throws Exception {
        System.setProperty("javax.net.debug", "all");
        // System.setProperty("javax.net.ssl.trustStore", "my-store.jks");
        // System.setProperty("https.protocols", "TLSv1,TLSv1.1,TLSv1.2");
    }
}

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\Programs\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)

SSL_VERSION=0

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = FALSE

TRACE_LEVEL_SERVER = USER

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\Programs\admin\wallet)
    )
  )

SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_AES_128_CBC_SHA, SSL_RSA_WITH_AES_128_CBC_SHA256, SSL_RSA_WITH_AES_128_GCM_SHA256)

ADR_BASE = C:\Programs\WINDOWS.X64_193000_db_home\log

Windows Service

Upvotes: 0

yolob 21
yolob 21

Reputation: 396

I encountered the same issue. What finally solved it for me was adding the permission on the .sso and .pk wallet file for the service account which oracle database uses in case Oracle Database was installed with a virtual account. i.e. NT Service\OracleService and NTService\OracleListener(These 2 accounts).

After enabling the permissions to full control for both account for .sso and .pk wallet file, I was able to establish the connection. You can leave the SSL_VERSION = 0 after making the above change. client and server can negotiate the best available protocol

Note: This answer is windows specific but the permission assignment was originaly suggested for the same kind of issue on a linux machine

Upate 05/02/2021. Dont use the wallet manager provided by oracle. Only use orapki. For some reason using wallet manager always screws the TCPS connection and due to unable to open the file error inspite of having all the required permissions provided to the virtual accounts if any

Upvotes: 0

pmdba
pmdba

Reputation: 7043

SSL_VERSION=3.1 is not valid. Valid values are "3.0" for SSL, or "1.0", "1.1", "1.2" for TLS. TLS is recommended, being the strongest.

https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-A2A81CEE-162D-4602-8315-990C8CC9E9E2

Upvotes: 1

Related Questions