Monday, February 6, 2012

Adventures in SQL*Net encryption (E-Business Suite edition)


This is a strange problem that we encountered at one of my clients recently. Basically, they're turning on the SQL*Net encryption features of Oracle Advanced Networking Option in their R12.1.3/11.2.0.3 environments. To do this, we're following note 376700.1 "Enabling SSL in Oracle E-Business Suite Release 12". In particular, Section 10 of that document.

Everything was working well. E-Business Suite was able to connect just fine, even our developers were connecting through TOAD. The only significant problem we encountered was with the Windows-based Workflow Builder 2.6.3.5 client. Here, despite a number of attempts at playing with the sqlnet.ora settings, we were consistantly getting the "ORA-12599 TNS:cryptographic checksum mismatch" error.

The mandate from our internal security team was that all outside traffic had to be encrypted. Since we have an external "DMZ" appsTier node, that meant that we had to turn on this SQL*Net encryption. For ALL of our environments.

Naturally, we opened a SR. As time drug on, we started talking about other options. Do we create one non-production environment entirely behind the firewall for the developers to use that doesn't have the encryption turned on? What about making them ssh to the box directly and use WFLOAD?

Finally, after a couple of months of back-and-forth with the support analysts, plenty of "level 16" traces, and a couple of debates, support was able to supply a solution.

With ANO, you specify params like this in the sqlnet.ora file:

#
# Server Settings
#
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,3DES112,3DES168)
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=MD5

In the case of the "ENCRYPTION_TYPES_SERVER", this is a list of encryption algorithms that the server will accept. The server will negotiate the strongest encryption algorithm with the client.

On the client, you would have similar parameters in the sqlnet.ora file (well, you don't
really need them because in many cases things will auto-negotiate based on defaults):
#
# Client Settings
#
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,3DES112,3DES168)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=MD5

Again, the ENCRYPTION_TYPES_CLIENT, this is a list of the algorithms that the client "can speak". Now, in the case of Workflow Builder 2.6.3.5 (which installs with a 10.1.0.2 client), it appears that the client is negotiating "in bad faith". Basically, it is allowing the negotiation to take place "normally". Based on that, the server will choose the strongest encryption from the ones common to both client and server. Unfortunately, the even though the client *says* it can handle AES256 (and others), it actually can't. So, for the Workflow Builder client, you are limited to the much older RC4_* encryption algorithms.

So, for Workflow Builder 2.6.3.5, here is what we have found to work for the client-side sqlnet.ora:

#
# Client Settings
#
SQLNET.ENCRYPTION_TYPES_CLIENT=rc4_256
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=MD5



You also need to make sure that the server-side sqlnet.ora contains the following line (or, at least contains the RC4_* encryption type you're intending to use):
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,3DES112,3DES168,RC4_256,RC4_128,RC4_56,RC4_40)



NOTE: In my testing, these algorithms work RC4_40, RC4_56, RC4_128, RC4_256 (we're using RC4_256 because it is the strongest of the RC4_* choices). The various RC_* encryption types are NOT mentioned in the sample sqlnet.ora provided by 376700.1 and, given their age, I would NOT recommend using them in a production environment.



– James

2 comments:

  1. Hello,
    I came to your blog and have been reading along your posts. I decided will leave my first comment. I have enjoyed reading your blog. Nice blog. I will keep visiting this blog very often…

    Pgp security

    ReplyDelete
  2. This is indeed a fantastic resource. Thank you for making this publicly available.


    Pgp

    ReplyDelete

Note: Only a member of this blog may post a comment.