Call: +44 (0)1904 557620 Call

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"] [Next entry: "Happy 18th Birthday Limited"]

TCPS Connection With an Oracle Instant Client

All of our products (PFCLScan, PFCLCode, PFCLObfuscate and PFCLForensics) 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


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 32bit instant client into this directory. I also copied the sub-directories vc8 and vc9 into the same directory:
Oracle client

The above is the basic instant client for 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 instant client directory these are shown highlighted here:
Oracle wallets

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

I added the instant client directory to my PATH:

This can then be seen in my command prompt:

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

My tnsnames.ora has the following content:

My sqlnet.ora has the following content:


TRACE_FILE_CLIENT = client.log
TRACE_DIRECTORY_CLIENT = c:\_aa\Oracle_client

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


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

pfclset.bat Release 1.0 Copyright 2015 Limited


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 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 - 64bit Production


And confirm its TCPS

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



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

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

This works!