Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "PL/SQL, AST, DIANA, Attributes and IDL"]

TCPS Connection With an Oracle Instant Client

All of our products (PFCLScan, PFCLCode, PFCLObfuscate and http://www.petefinnigan.com/products/pfclforensics.htm) can use an Oracle instant client to connect to the target database(s) or even a full client.

It is of course simpler to use an instant client if you don't have a full client on your PC as it is just a case of unzipping the instant client and copying the files and sub-directories (usually vc8, vc9 or vc14) to the place you want to use it.

One issue with an instant client is that until 19c it doesn't natively support TCPS and wallets with the EZCONNECT syntax. When you use 19c there is additional syntax to support this (I will do an additional blog post for this soon).

This blog post is based on making a TCPS connection to the database initially with SQL*Plus to prove the connection works and then with PFCLScan to show that we can connect in PFCLScan with a TNS alias and an instant client but also we can make a TCPS connection to the database/listener.

The following details this process and shows how PFCLScan works with this setup

Introduction


This is a short description to cover connecting from PFCLScan and also SQL*Plus from a client PC to a database that has SSL encryption setup.

This is also a test with an instant client and not a full Oracle client

This will also work with cloud setups and an autonomous database for instance. In this test case we instead made an SSL connection to a 12.2 SE Oracle database with SSL encryption and a self signed certificate and the same certificate in the client/server wallets for ease of setup. But this set up allows TCPS connection to the database and proves that the TCPS connection works.

Server Setup


I am not going to describe the server setup in detail but can provide copies of my sqlnet.ora, listener.ora and orapki commands if needed.

I set up a TCPS connection setup in my 12.2 single instance SE2 database. This uses port 2484 for the TCPS and has a self signed certificate in a wallet on the server. This wallet is then referenced in my sqlnet.ora and listener.ora on the server.

As I am using an instant client on the PC I do not have orapki to set up the client wallet as the instant client does not ship this. So, I created the wallet and installed the certificate on the server and then copied the wallet files to the PC for the client to use.

Client Setup


I created a directory on my PC and copied the 11.2.0.4 32bit instant client into this directory. I also copied the sub-directories vc8 and vc9 into the same directory:

Oracle instant client copied to PC


The above is the basic instant client for 11.2.0.4 and also the sqlplus add on. They are both zip files so just copy the contents to the directory.

I copied the wallet files from the server and copied them into my client 11.2.0.4 instant client directory these are shown highlighted here:

Oracle wallets copied from the server



I then set my TNS_ADMIN environment variable to point to my 11.2.0,4 client folder: c:\_aa\oracle_client:

TNS_ADMIN environment variable



I added the 11.2.0.4 instant client directory to my PATH:

Set The PATH environment variable



This can then be seen in my command prompt:

Show The PATH environment variable



I now created my sqlnet.ora and tnsnames.ora and copied these also to the same 11.2.0.4 client folder these are shown highlighted:

sqlnet.ora and tnsnames.ora setup



My tnsnames.ora has the following content:

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.1.95)(PORT=2484))
(CONNECT_DATA=
(SERVICE_NAME=orcl.localdomain)))
My sqlnet.ora has the following content:
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION=1.0
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=(DIRECTORY=C:\_aa\Oracle_client)))

TRACE_LEVEL_CLIENT = support
TRACE_FILE_CLIENT = client.log
TRACE_DIRECTORY_CLIENT = c:\_aa\Oracle_client
DIAG_ADR_ENABLED = OFF
ADR_BASE = OFF

Testing the Connection
I can now use SQL*Plus to connect to the database. First I start a CMD prompt and CD to the scanner BIN directory (the location of oscan.exe):

C:\>cd _aa\PB\bin

C:\_aa\PB\bin>dir oscan.exe
Volume in drive C is OS
Volume Serial Number is C67F-6487

Directory of C:\_aa\PB\bin

04/11/2020 11:26 1,496,064 oscan.exe
1 File(s) 1,496,064 bytes
0 Dir(s) 76,606,251,008 bytes free

C:\_aa\PB\bin>

Now I run the pfclset.bat script. This moves me to the DATA directory:

C:\_aa\PB\bin>pfclset
pfclset.bat Release 1.0 Copyright 2015 PeteFinnigan.com Limited

C:\_aa\PD>

Now I can connect to my database using SQL*Plus and the TCPS connection to prove the connection works for the client:

C:\_aa\PD>sqlplus system/oracle1@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 10:21:00 2020

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


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL>

And confirm its TCPS

SQL> select sys_context('USERENV','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL>

Now I can set up a project in PFCLScan and set the connection details to use my tnsnames.ora alias ORCL:

connection details PFCLScan



I am able to connect from an 11.2.0.4 instant client using tnsnames.ora alias and making a TCPS connection to my 12.2 database
Now I can run the scan:

completed scan with PFCLScan



This works!