This is the last post in this series, in which I have described configurations for the server wallet, server networking, client networking, and database. If you have completed all of the steps I laid out, then you are ready to test your SSL connection using your smart card.
TNS Ping over TCPS
First confirm that the client is able to tnsping the alias for the TCPS listener. This will confirm that the client wallet is properly accessible by the client, and that SSL encryption between the client and server is working. On the Windows client, open a PowerShell window and enter the following:
PS> tnsping [SERVICE_ALIAS]
You should be prompted by Windows to select a certificate. Once you select the appropriate ID or PIV client certificate from the “Select a Certificate” popup, you should see output like this:
TNS Ping Utility for 64-bit Windows: Version 188.8.131.52.0 - Production on 17-JAN-2018 13:47:55 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: C:\Oracle\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS) (HOST=database1.us.af.mil) (PORT=2484)) (CONNECT_DATA= (SERVICE_NAME=tools.us.af.mil))) OK (2000 msec)
Do not be surprised if the response time seems quite large. The elapsed time shown includes the amount of time it takes the user to react to the prompt and select a certificate, so it will always be several seconds.
Next, connect through SQL*Plus as the TCPS authenticated user. This will confirm SSL encryption and user authentication to the database.
PS> sqlplus /@[SERVICE_ALIAS] SQL*Plus: Release 184.108.40.206.0 Production on Wed Jan 17 13:55 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options SQL>
You should get a SQL prompt. Now confirm that you are connected as the expected user, and that the tcps network protocol is being used:
SQL> show user USER is "JDOE" SQL> select sys_context('userenv','network_protocol') from dual; SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') ----------------------------------------------------------------- tcps
Configuring Additional Applications
TOAD can be configured to use certificate authentication (or traditional Oracle Wallet authentication) just by entering “EXTERNAL” in the username prompt. The password field can be left blank in most cases, or you can enter any text you want to as a place filler if the connection dialog requires a value.
SQL Developer version 4.1.1 or later is required to work with the TCPS connection. Preferences settings are required in the application that are not available in earlier versions. The current SQL Developer version can be downloaded from the public sector of oracle.com. Note that the SQL Developer version bundled with the Oracle 18.104.22.168.0 client is an earlier version, so a separate SQL Developer download is required to obtain the later version.
SQL Developer by default uses JDBC connections, which do not work with this TCPS client setup. To override this, go to Tools->Preferences->Database->Advanced. Check the “Use Oracle Client” and “Use OCI/Thick driver” boxes, browse for the Oracle Home directory of the Oracle Client, and set the Tnsnames directory. When creating a connection, select Connection Type TNS, then select the network alias of the TCPS connection. Then check the OS Authentication box. The connection will then not require a username/password.
The Oracle Connection Manager and Database Firewall (AVDF) products do not support SSL connections. SSL clients must be able to connect directly to the database server. If your security plan requires the use of either of these products then you must use Oracle native encryption for Connection Manager, or no encryption for Database Firewall.