Wednesday, March 21, 2018

Oracle Native Encryption and Integrity on a Glassfish JDBC Connection Pool

When encrypting a connection to an Oracle database, there are two primary choices.  You can use SSL/TLS (which creates a TCPS connection) or you can use Oracle Native Encryption.  As with web servers, creating a TCPS connection requires SSL Certificates that are accepted by both the client and the database server as well as creating wallets to store them.  Oracle native encryption, on the other hand, only requires some quick configuration changes.  On the whole, it is much easier to implement.

How It Works

Oracle native encryption encrypts your data before sending it to the client.  It still uses a TCP connection (which is not encrypted) but the data flowing through it is encrypted.  As of 11g, Oracle supports the following encryption algorithms:

  • AES256 (AES 256-bit key)
  • AES192 (AES 192-bit key)
  • AES128 (AES 128-bit key)
  • 3DES168 (3-key 3DES)
  • 3DES112 (2-key 3DES)
  • DES56C (DES 56-bit key CBC)
  • DES40C (DES 40-bit key CBC)
  • RC4_256 (RC4 256-bit key)
  • RC4_128 (RC4 128-bit key)
  • RC4_56 (RC4 56-bit key)
  • RC4_40 (RC4 40-bit key)
Once you have selected your preferred encryption algorithm, you can set either your server or client to treat connections as follows:
  • ACCEPTED : The client or server will allow both encrypted and non-encrypted connections. This is the default if the parameter is not set.
  • REJECTED : The client or server will refuse encrypted traffic.
  • REQUESTED : The client or server will request encrypted traffic if it is possible, but will accept non-encrypted traffic if encryption is not possible.
  • REQUIRED : The client or server will only accept encrypted traffic.
The easiest way to set it up is to configure either your database or your client (any client using the Oracle client libraries) by adding parameters to your sqlnet.ora file.  For example:



These lines will require any outgoing (client) or incoming (server) connections to be encrypted using 3DES168 encryption.

Further, there are two lines that enable data integrity checking.  Data integrity creates a checksum to ensure that the data/message/request that the database receives is the same as what the client sent.  This protects against the following attacks:
  • Data Modification
  • Deleted Packets
  • Replay Attacks
The configuration follows the same rules as encryption.  It supports the following checksumming algorithms:
  • SHA1
  • MD5
It also has the same preference/requirement parameters:
  • ACCEPTED : The client or server will allow both checksummed and non-checksummed connections. This is the default if the parameter is not set.
  • REJECTED : The client or server will refuse checksummed traffic.
  • REQUESTED : The client or server will request checksummed traffic if it is possible, but will accept non-checksummed traffic if checksumming is not possible.
  • REQUIRED : The client or server will only accept checksummed traffic.

JDBC Thin Clients

Unfortunately, JDBC Thin Clients do use the standard Oracle client libraries and so any configuration in the sqlnet.ora file on your client machine will be ignored.  So how do you enable encryption and integrity over JDBC?  There are two methods.  You can add it to your JDBC connection creation code or you can enable it as part of a server configured JDBC connection pool.

There is lots of documentation on how to enable encryption and integrity checking in your java code.  What I had a hard time finding, is how to enable it in your JDBC connection pool configuration - specifically in your GlassFish server configuration (I was able to find WebLogic help here).  Apparently, nobody does it.

So after a LOT of searching, I was able to find the clues I needed on My Oracle Support in Doc ID 1664506.1

Encryption and Integrity in a GlassFish Connection Pool

When you create a JDBC Connection Pool in GlassFish 4, you go through a very simple creation wizard with some very basic options.  First you create the name and type of connection.  For example, the following creates an Oracle connection pool called Example Pool.

Once you've completed the first step, you move on to step 2 by clicking Next.  On step 2 you'll have a bunch of default values which you can leave the same but if you scroll down to the bottom, you'll get to a section called Additional Properties.  This is where you will enter in your encryption and integrity options.  You do it by clicking the Add Property button and using the following;
  • Name: ConnectionProperties
  • Value: (,,3DES168),,,SHA1))

This breaks down into essentially 4 commands:
So, encryption is required and AES128 and 3DES168 are acceptable algorithms.  Also, data integrity checking is required and MD5 and SHA1 are acceptable algorithms.

NOTE: Make sure your whole command is surrounded by parenthesis and that there are commas separating the individual commands. 

As a quick reminder, you'll need to put in a User, Password, and URL.  The URL will be in the form of: 



How can you tell if it worked?  The following SQL will give the session's network service banner which will show if encryption and integrity are being used.

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

It will show an output similar to this:

TCP/IP NT Protocol Adapter for Linux: Version - Production
Encryption service for Linux: Version - Production
AES256 Encryption service adapter for Linux: Version - Production
Crypto-checksumming service for Linux: Version - Production
MD5 Crypto-checksumming service adapter for Linux: Version - Production

The lines in bold show that the connection is using AES256 encryption and MD5 checksumming.  A network service banner with no encryption or integrity checking would look like this:

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

Observed Behavior

I couple of quick observations in closing.  When there are no client side settings for a JDBC connection, I've observed the following:
  • If encryption or integrity is set to REQUIRED on the database server, the connection will be encrypted and checksummed.
  • If encryption or integrity is set to REQUESTED on the database server, the connection will NOT be encrypted or checksummed (this is counter-intuitive as the connection should be encrypted and checksummed if possible but on my JDBC connections, they were not).

Monday, December 4, 2017

Windows Task Scheduler Job Fails After Password Change

I've got a simple powershell script that I use to back up some files every week.  I use the Windows Task Scheduler to run the job automatically. 

Last week I changed my password (I use an Active Directory domain account) and over the weekend, my job failed.  At first I didn't see the correlation and the error was not helpful:

Task Scheduler failed to start "\Backup Security Files" task for user "". Additional Data: Error Value: 2147943726.

I tried running the script from the command line and it worked fine.  So I used Bing to search the error.  It came up with this link which explained that it was the password change which causes the issue.  The solution said to do the following:

  • In Task Scheduler, right click on the scheduled job and select  Properties >>> then select the Settings tab
  • In the last listed option:  "If the task is already running, the following rule applies:", select "Stop the existing instance" from the drop down list.
  • Click "OK"
  • You will be prompted to put your password in. 
Once you've put your new password in, the job is fixed and should run fine.

Monday, April 6, 2015

Installing Oracle 32 ODBC Drivers from the 12c Client

When you install the 32 bit Oracle 12c client on a Windows 64 bit server, the ODBC drivers don't show up.  You actually have to run the following from the command line:


Once you run it, the ODBC drivers work fine.

Thursday, September 25, 2014

Windows 8.1 and the Cisco VPN Client

Cisco stopped updating their VPN client a long time ago but there are many of us that continue to use it.  Mostly because we're required to.  I recently updated my home laptop to Windows 8.1 and my VPN client stopped working.  I tried uninstalling and reinstalling multiple times but that didn't help at all.  I did quite a few internet searches as well and was getting nowhere.  Finally, I found a forum entry that solved the issue for me.  It has to do with DNE (which I don't know what it stands for but it has to do with low level network functionality and is used with VPN clients).  Here's how it worked:

  1. I uninstalled my Cisco VPN client.
  2. I went to the site: (I thought it amusing that I'm using a Citrix solution to a Cisco problem)
  3. I downloaded and ran the winfix.exe program (
  4. I rebooted my computer.
  5. I downloaded and installed the latest DNE (
  6. I reinstalled the Cisco VPN client.
That fixed my issue and I am now able to VPN in to work.

Wednesday, June 5, 2013

SOA - What and Why

Service Oriented Architecture (SOA) is more of a mindset than a product.  In fact there are many different products you could use for SOA and you could also use none.  It depends on how you implement it.  In other words, it is a philosophy more than it is a product.  Yes, there are many products that follow the philosophy but having a product doesn't make SOA successful as much as following the philosophy does.
SOA, as I think of it, is about standardizing and managing your integrations.  The goal is to create an accounting of what you have, eliminate tribal knowledge (you know, the stuff that only this one techy guy knows), and simply integration management.  It's the idea that data flow from one application to another, be it legacy application or cloud application or something else, should be done in a standards-based way, a way that is not dependent on a particular application or vendor.  My most common example is web services. 
Web services are based on standards, the are created and maintained in a consistent manner, and they developed/administered outside of the source and target applications.  So now you can have a single team responsible for integration instead of one resource here and another over there, etc.  It also means that if resource A leaves, resource B can pick up where A left off and not have to try to figure out what and how A got these two things to talk to each other.  That's one benefit of SOA, the reduction or even elimination of tribal knowledge.
Unfortunately, web services is not going to be supported by every single application.  That's why there's enterprise service buses (ESBs).  These work like translation layers.  They talk to legacy applications in a way they understand and convert that data into your integration standard (like web services).  On top of that, they are a tool for monitoring your integrations as well and catching issues before they become problems. 
Another aspect of SOA is governance or change management.  Ever had an application break because something it depended on changed?  You know the stress of trying to find out what broke and why it broke while people are freaking out because some widget is down?  Effective SOA governance can eliminate almost all of that.  Yes it's a pain to track down and effectively document all of your applications and integrations.  Yes it's a cultural shift (and not necessarily a popular one) to record any new or changed integrations.  But, once in place, good governance can take care of many problems before they become problems.
So, that's my take on SOA in a nutshell: standards-based integration and good integration governance.