In this article, I will explain how to set up an encrypted communications channel in Oracle Database. This is the third in a series of blog posts I've published about encryption as it relates to databases. Previously, I covered encrypting network communications between the client and the database server and data at rest.
Oracle Database allows you to configure encryption of client-server channel in two ways: using native encryption and using SSL. Let's examine how to configure and debug each of them for an OCI client application, more specifically for sqlplus. This assumes there is no Oracle software installed on the client machine initially.
G:\instantclient_12_1)Please note that in this post Oracle Database 12c Enterprise Edition is used on the server.
First, create the sqlnet.ora configuration file on the client, if one does not exist already. You can use tools like Process Monitor to locate the file:
For our sqlplus example it will be:
G:\instantclient_12_1\network\admin\sqlnet.ora
Next the file should be updated with encryption-specific information:
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.CRYPTO_CHECKSUM_CLIENT = required
This will result in communications with the server using encryption and check-summing. Confirm this by examining network capture before and after sqlnet.ora change and by reading Oracle Client Trace file:
2015-08-18 10:34:24.112973 : naeecom:entry
2015-08-18 10:34:24.112973 : naeecom:The server chose the 'AES256' encryption algorithm
2015-08-18 10:34:24.112973 : naeecom:exit
2015-08-18 10:34:24.112973 : naeccom:entry
2015-08-18 10:34:24.112973 : naeccom:The server chose the 'MD5' crypto-checksumming algorithm
2015-08-18 10:34:24.112973 : naeccom:exit
2015-08-18 10:34:24.112973 : na_tns:entry
2015-08-18 10:34:24.112973 : na_tns:Secure Network Services is available.
2015-08-18 10:34:24.112973 : nau_adi:entry
2015-08-18 10:34:24.112973 : nau_adi:exit
2015-08-18 10:34:24.112973 : na_tns: authentication is not active
2015-08-18 10:34:24.112973 : na_tns: encryption is active, using AES256
2015-08-18 10:34:24.112973 : na_tns: crypto-checksumming is active, using MD5
To examine the trace, we need to prepare a directory for it. Otherwise it will go to inconvenient locations (i.e., nested deep in C:\WINDOWS\). So create this directory under sqlplus location:
log\diag\clients
And add this line to the sqlnet.ora:
TRACE_LEVEL_CLIENT = supportNow after running sqlplus, look for *.trc files under the trace directory nested under the log\diag\clients directory created above. Although you can easily explore *.trc files in a text editor, Oracle provides a tool called trcasst that produces more readable output from trace files:
trcasst ora_6100_6316.trc > ora_6100_6316.txt
Here's a snippet from ora_6100_6316.txt:
---> Send 143 bytes - Data packet timestamp=015-09-02 13:19:11.545093
Native Services negotiation packet version#: 0
Service data packet #0 for Supervisor has 3 subpackets
Subpacket #0: Version #202375680
Subpacket #1: 0000000000000000
Subpacket #2: DEADBEEF0003000000040004000100010002
Service data packet #1 for Authentication has 3 subpackets
Subpacket #0: Version #202375680
Subpacket #1: UB2: 57569
Subpacket #2: FCFF
Service data packet #2 for Encryption has 2 subpackets
Subpacket #0: Version #202375680
Subpacket #1: 11
Service data packet #3 for Data Integrity has 2 subpackets
Subpacket #0: Version #202375680
Subpacket #1: 01
For debugging encryption, however, examining trc files is better since more encryption-specific information is available within them rather than in the trcasst processed output.
SSL communication is configured in Oracle by creating two wallets (client and server) that will store certificates and modifying sqlnet.ora, tnsnames.ora and listener.ora configuration files.
Using the orapki utility, the process is relatively easy. See Document 401251.1 (Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki) on Oracle's support website for details of each orapki option. Here I'll just post step-by-step instructions.
Setting up SSL requires certificates to be signed by a trusted certificate authority (CA). For development and testing purposes it is convenient to use self-signed certificates. Next we will configure the server and the client separately using a self-signed certificate.
Create a server wallet with autologin property turned on:
orapki wallet create -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet -auto_loginAfter this command is executed (it will prompt you for a password - choose a good one) a directory named db_wallet will be created along with the following files:
-rw------- 1 oracle oinstall 120 Aug 31 18:24 cwallet.sso
-rw-rw-rw- 1 oracle oinstall 0 Aug 31 18:24 cwallet.sso.lck
-rw------- 1 oracle oinstall 75 Aug 31 18:24 ewallet.p12
-rw-rw-rw- 1 oracle oinstall 0 Aug 31 18:24 ewallet.p12.lck
cwallet.sso* files are only created when the auto_login option is used and contains an "obfuscated copy of the wallet" per Oracle documentation.
Now add a self-signed certificate to the wallet and export it for subsequent import on the client:
orapki wallet add -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet -dn 'CN=root_test_CA,C=US' -keysize 2048 -self_signed -validity 10 -pwd ...
Examine the wallet contents:
orapki wallet display -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet
...
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=root_test_CA,C=US
Trusted Certificates:
Subject: CN=root_test_CA,C=US
...
Notice the Trusted Certificates part - it has the root_test_CA certificate now. Let's export it to a file:
orapki wallet export -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet -dn 'CN=root_test_CA,C=US' -cert /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet/root_test_CA.cert
Our next step is to create the server certificate using the orapki utility, which will be presented to a client during SSL handshake phase. However for a simple test, one may use self-signed root certificate as a server certificate so I'll skip this step.
Now it's time to update the sqlnet.ora file with the server wallet location:
WALLET_LOCATION=
(SOURCE=
(METHOD=file) (METHOD_DATA=(DIRECTORY=/home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet)))
And add this line since we are not going to use client authentication:
SSL_CLIENT_AUTHENTICATION = FALSE
Finally, to configure the server for SSL we must add TCPS entry to the listener.ora file:
...
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.101)(PORT = 2484))
...
Then add a link to the server wallet (yes, the same entry as in sqlnet.ora file):
WALLET_LOCATION=
(SOURCE=
(METHOD=file) (METHOD_DATA=(DIRECTORY=/home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet)))
Restart the listener after these changes or reload its configuration.
In our example we do not have orapki on the client machine, so client wallet creation and trusted certificate import could be done on the server, and then the wallet files should be copied to the client.
Creating the client wallet:
orapki wallet create -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/client_wallet -auto_login -pwd ...
Import the root CA certificate:
orapki wallet add -wallet /home/oracle/app/oracle/product/12.1.0/dbhome_1/client_wallet -trusted_cert -cert /home/oracle/app/oracle/product/12.1.0/dbhome_1/db_wallet/root_test_CA.cert -pwd ...
This will allow the client to validate the server's certificate since now both client and server have one trusted certificate. Copy contents of the client_wallet directory to the client and update the client's sqlnet.ora to point to the wallet files just copied:
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=(DIRECTORY=G:\ instantclient_12_1\client_wallet)))
Now, to instruct our OCI application (sqlplus) to use SSL we must create an entry (alias) in the tnsnames.ora file on the client that will resolve the connection identifier so that TCPS protocol is used instead of TCP. Create a file named tnsnames.ora under network\admin\ with content like the following:
REMOTE_SSL_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.101)(PORT = 2484))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME = orcl12c1)
)
)
Make sure that all round brackets are in place: if you type it in manually with typos, the connection attempt will fail with errors like "ORA-12154: TNS:could not resolve the connect identifier specified". That is why sometimes using a GUI tool is beneficial :).
Now try to connect:
sqlplus -L system@REMOTE_SSL_DB
If the connection fails, examining the trace file corresponding to this failure (see log\diag\clients\user_...\host_..\trace) is very helpful. Search for the words "SSL" and "error."
To verify that we are connected via SSL, run this query in sqlplus:
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
----------------------------------------------------------------------
tcps
Notice you can perform additional SSL configuration on the client: selecting which algorithms to use for authentication, enabling encryption and data integrity checks, selecting which SSL version to use and some others. For example, this sqlnet.ora fragment instructs the client to use version 3.0 of SSL and use SSL_RSA_WITH_DES_CBC_SHA cipher suite:
SSL_VERSION = 3.0
SSL_CIPHER_SUITES = (SSL_RSA_WITH_DES_CBC_SHA)
Note that both the client and the server must support the SSL version and cipher suites specified for the connection to succeed. Again, the best source to verify what setting has been used is the client and server trace.
Examine SSL_VERSION value in sqlnet.ora file: if set, it must be 3.0 or 1.0 or 1.1 or 1.2 or certain combinations of these values (see https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF235 for details).
If the client trace file contains lines like these:
nioqper: ns main err code: 12560
nioqper: ns (2) err code: 0
nioqper: nt main err code: 28862
nioqper: nt (2) err code: 542
nioqper: nt OS err code: 0
Examine SSL_VERSION value in sqlnet.ora on both the client and server: if set, there should be a match.
If client trace file contains lines like these:
nioqper: error from nscall
nioqper: ns main err code: 12560
nioqper: ns (2) err code: 0
nioqper: nt main err code: 28860
nioqper: nt (2) err code: 542
nioqper: nt OS err code: 0
Examine SSL_CLIENT_AUTHENTICATION setting in the server sqlnet.ora file: it must be explicitly set to FALSE for SSL configuration without client authentication (our example with sqlplus).
OCI client cannot open the wallet file. Check the syntax in the sqlnet.ora file and then the actual wallet files. The trace contains lines like this:
ntzlogin:Wallet open failed with error 28759
ntzlogin:returning NZ error 28759 in result structure
ntzlogin:failed with error 540
Make sure tnsnames.ora on the client has the correct alias for the SSL database.
There are many other possible errors, and here we only listed some of the most common. Again, examining the trace file coupled with some filesystem/network monitoring is usually sufficient to resolve the problem.
Trustwave database scanning products contain a variety of Oracle configuration checks that help ensure encryption is configured properly.