Welcome To Support Community

Pipeline Pilot and BIOVIA Foundation

Advanced Search
Ask Search:
Joe_BJoe_B 

Use of SSL and Native Network Encryption with Oracle connection in Pipeline Pilot

Hi,

Is it possible to setup and use SSL/TLS and / or Native Network Encryption with Oracle in Pipeline Pilot (https://oracle-base.com/blog/2015/06/27/native-network-encryption-not-part-of-advanced-security-option/).
I'm more interested in using SSL to encrypt connections then the client / server authentication part.
Before you had to pay extra to use this in Oracle, but with recent licence term changes its available as standard from Oracle 11 onwards. I've looked in the PP admin portal (9.5) for data sources and couldn't see any options for this or information on setting it up.

I know when running behind your own network and firewall not using encrption is not such a big problem. However sometimes you need to go outside and I'd like this option as well as other measures already taken.

Is it possible with PP?

Thanks

Joe
TonTon (Accelrys, Inc.) 
Hi Kevin -
Here is a response I got from our technical team. Hope that is helpful.
Ton

I don’t have any experience with it, but the referenced oracle KB document 762286.1 makes it seem pretty straight-forward to enable SSL between a database and a full Oracle client (i.e., sqlplus, tnsnames.ora, etc). 
 
I’m a bit fuzzier on the JDBC thin client stuff, but if I’m reading it right, it would be a matter of
  •          creating the server and client keys as in the full client case
  •          Getting oraclepki.jar from the database server and dropping it in the java classpath on the PP server
  •          Adding a PP Data Source using the oracle.jdbc.OracleDriver
  •          Here’s where I’m fuzziest:
    •    Create a properties file something like:
      •   javax.net.ssl.trustStore=D:/Wallets/client_wallet/ewallet.p12
        javax.net.ssl.trustStoreType=PKCS12
        javax.net.ssl.trustStorePassword=myclient123
    •    Somehow communicate that properties file to the Data Source.  The example in the KB document is running a tester class directly with java;  I don’t know how that translates to PP:
      •   java -classpath .;.\lib\ojdbc6.jar;.\lib\oraclepki.jar JDBCSSLTester test1.properties
 
Is that remotely helpful?
 
SteveSteve (BIOVIA) 
I have no way of testing this currently, but you should be able to set up the connection via either a server based ODBC DSN or via Connection settings in the Pipeline Pilot Admin Portal for the Data Connection set-up. 

For a DSN: from the Windows ODBC Data Source Administrator, add a System DSN with the driver of BIOVIA Oracle 7.1.  Set up your various settings (name, host, port, SID, service name, etc.... advanced options per our documentation EnableSQLDescribeParam, etc).  Then on the Security tab, set "Encryption Method" as required for your connection along with all the suboptions. The DSN should automatically be imported into your PP server instance as an available data source.

Alternatively, a PP datasource can be modified using the Connection Settings parameter on the admin portal page.  You would need to specify the appropriate values for EncryptionMethod, ValidateServerCertificate, TrustStore, TrustStorePassword, KeyStore, KeyStorePassword, KeyPassword, and HostNameInCertificate.  (basically, the same values that are set via the DSN configuration above)

Hope this helps.
Joe_BJoe_B
Thanks Ton and Steve,
I like Steve’s approach of using the Windows ODBC Data Source Administration as it will store the password for you – rather than using the connection setting field which is unencrypted and exposes the passwords.  Fortunately my server runs on windows which makes this an option.
If a similar setup was on RHEL do you know if PP would be able to access the system drivers installed with unixODBC? – see http://stackoverflow.com/questions/13922415/how-do-i-setup-oracle-odbc-drivers-on-rhel-6-linux
If I go down the SSL route I’m looking for something that can work with both RHEL and Windows.

Thanks

Joe
 
Joe_BJoe_B

Hi Steve,

Thanks for your tips. I finally got round to this and it worked a treat first time for windows using Native Network Encryption (NNE) (https://oracle-base.com/articles/misc/native-network-encryption-for-database-connections) instead of dedicated exchanged SSL/TLS certificates

However I'm struggling to replicate this on linux (RHEL6).

The odbc.ini file on linux (PP9.5) has been modified as per your instructions and the data direct guide.

Specifically the AuthenticationMethod=1, GSSClient=native, EncryptionMethod=0 and EnableDescribeParam=1.   This should enable NNE encryption and appears to be  the same settings used for the windows machine, which can connect to an oracle database that has been set to insist on encrypted authentication and reject non encrypted.

 

However we get the error message "Connection Dead" which suggests that the AuthenticationMethod is not encrypted from RHEL (we can verify that by disabling the force NNE on the oracle DB and it then works)

The windows options that work are shown below along with the connection dead error for RHEL

User-added image

While the  data direct odbc.ini on the RHEL is..

 

[JB_NNEtest Protocol]

Driver=/opt/accelrys/aep91packaged/apps/scitegic/core/packages_linux64/datadirect/lib/PPora27.so

Description=BIOVIA 7.1 Oracle Wire Protocol

AlternateServers=

ApplicationUsingThreads=1

AccountingInfo=

Action=

ApplicationName=

ArraySize=60000

AuthenticationMethod=1

BulkBinaryThreshold=32

BulkCharacterThreshold=-1

BulkLoadBatchSize=1024

BulkLoadFieldDelimiter=

BulkLoadRecordDelimiter=

CachedCursorLimit=32

CachedDescLimit=0

CatalogIncludesSynonyms=1

CatalogOptions=0

ClientHostName=

ClientID=

ClientUser=

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

DefaultLongDataBuffLen=1024

DescribeAtPrepare=0

EditionName=

EnableBulkLoad=0

EnableDescribeParam=1

EnableNcharSupport=0

EnableScrollableCursors=1

EnableStaticCursorsForLongData=0

EnableTimestampWithTimeZone=0

EncryptionMethod=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchTSWTZasTimestamp=0

GSSClient=native

HostName=xxxxxxxxxxxxxxxxxxxx

HostNameInCertificate=

InitializationString=

KeyPassword=

KeyStore=

KeyStorePassword=

LoadBalanceTimeout=0

LoadBalancing=0

LocalTimeZoneOffset=

LockTimeOut=-1

LoginTimeout=15

LogonID=

MaxPoolSize=100

MinPoolSize=0

Module=

Password=

Pooling=0

PortNumber=1521

ProcedureRetResults=0

ProgramID=

QueryTimeout=0

ReportCodePageConversionErrors=0

ReportRecycleBin=0

ServerName=

ServerType=0

ServiceName=

SID=ORCL

TimestampeEscapeMapping=0

TNSNamesFile=<tnsnames.ora_filename>

TrustStore=

TrustStorePassword=

UseCurrentSchema=1

ValidateServerCertificate=1

WireProtocolMode=1

 

Can you suggest what might be wrong? For windows we don't need a trust store or to import keys and my understanding of NNE is that we shouldn't need to.

 

Thanks

Joe

DanielDaniel (BIOVIA) 
Hi Joe,
if you are not bound to ODBC you might try the steps to configure a JDBC based and SSL-secured connection as outlined in the attached document. It worked fine for me, the creation of the required Oracle Wallet is however a bit limited. The Oracle Wallet finally is a standard PKCS#12 file but certificate aliases and other parameters seem to require standardized values, so I did not find a way to create the Oracle Wallet using standard tools like OpenSSL. So unfortunately I did not manage to create an Oracle Wallet using already existing certificates/private keys for the Oracle Server, I had to request a new certificate. This also prevented me from adding e.g. DNS Subject Alternative Names to the Certificate Signing Request which might come in handy if you want to expose a server to the public using an alias machine name.

I think that it should be possible to create the wallet without Oracle tools if you thoroughly inspect the parameters used in an existing wallet file created by the Oracle Wallet manager.

I have to admit that I did not try out this procedure for a while now, so I do not recall exactly in which format the certificate chain needs to be loaded into the Oracle Wallet manager. It might be that you require single certificate and key files in the PEM format, but it also might be that the Wallet manager can load PKCS#7 and/or PKCS#12 files. I'm happy to have another look at this if required.

Regards
Daniel
 
Joe_BJoe_B
Thanks Daniel,

I’m aware of this approach, but it’s the approach I want to avoid rather than use. As you alluded to, the key generation and uploading is a complex multi-step process, involving companies generating and exchanging keys (which will eventually expire).

It also assumes a level of access and control over keys and oracle server access that may not always be possible / desirable.

My understanding of using NNE is it should avoid all this pain as you don’t need to generate and exchange specific keys.

The fact it worked for me on a windows PP server so simply and allowed me to connect to a DB setup to only allow encrypted connections is a good proof of concept it works, and also says to me it should work on PP linux.

I believe that datadirect is handling the connection (which is part of PP), so if it works with PP windows it should also be possible to get it to work with PP linux.

Thanks

Joe