Verify if the client is connecting to the Oracle database using native network encryption

All we need is an easy explanation of the problem, so here it is.

My environment is as below –

Server: Oracle 12C 12.1.0.2.v17 ( AWS RDS Service)

Client: Windows 2012 machine with Oracle 19C 64 bit full client

Tools : SQL Developer and Toad on the client machine

I have configured native network encryption for the RDS service by following these instructions.

I set following options on the RDS –

SQLNET.ENCRYPTION_SERVER= Accepted
SQLNET.ENCRYPTION_TYPES_SERVER= AES256

The client Oracle 19c 64 bit home is
C:\oracle\product\19.0.0\client_1\network\admin\sqlnet.ora

Both client tools using the TNS name in the above path; I verified this by using tnsping

The sqlnet.ora in client has following options –

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

After the above configurations, I restarted my windows machine (just to make sure) and am able to establish connection to the RDS with Toad and SQL Developer tool.

MY question: How do I verify if my connection is encrypted ?

I tried executing below query –

select NETWORK_SERVICE_BANNER
from v$session_connect_info
where SID = sys_context('USERENV','SID');

The output I am seeing is

TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production

Encryption service for Linux: Version 12.1.0.2.0 - Production

Crypto-checksumming service for Linux: Version 12.1.0.2.0 - Production

SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 - Production

I do not see "AES256 Encryption" in the NETWORK_SERVICE_BANNER output.

Related question: I need all the client connections from this machine to my server use encryption. Is there a way I can enforce this by using logon trigger ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You’re confused with my comments,with accepted on server side and required client side sqlplus connection is encrypted and Sql Developer with thin client is unencrypted but the same Sql Developer with thick client connection is encrypted.(Toad is out of scope I never used at all).In other words accepted on server side is working as expected.

Let me demonstrate for you

 sqlnet.ora on server 

    $ cat $TNS_ADMIN/sqlnet.ora
    # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.
    
    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
    
    SQLNET.ENCRYPTION_SERVER = accepted
    
    SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)
    
    SQLNET.CRYPTO_CHECKSUM_SERVER = required
    
    [CDB2] [email protected]:~
    
   Client side Window 10 Pro

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\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= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#SQLNET.ENCRYPTION_CLIENT=REQUIRED   -- commented out
#SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256) -- commented out



    SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production

Now I’ll uncomment last two lines in sqlnet.ora client side

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\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= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)




ADR_BASE = C:\app\oracle\product\19.3.0\db_1\log


SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production.

Now let’s test with Sql Developer thin client

Verify if the client is connecting to the Oracle database using native network encryption

It’s evident from above image it uses jdbc thin driver.Let’s check the sql out put in Sql Developer and as expected there is no AES256 encryption

Verify if the client is connecting to the Oracle database using native network encryption

Now I will switch to thick client,check both images
Verify if the client is connecting to the Oracle database using native network encryption

Verify if the client is connecting to the Oracle database using native network encryption

Hope this clears up

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply