Share:|

In this third post on encryption we're going to show how to enable SSL between an AR System server and its Oracle database.  In previous posts we've seen how to use Oracle's native encryption and SSL with Microsoft SQL Server. The process we're going to follow is similar to the latter.

 

Again, the high level steps are:

 

  • obtain a certificate
  • configure the database to use the certificate
  • import the certificate on the client
  • configure the AR System server to use SSL

 

Oracle databases store their certificates in a set of files called a wallet so, unless you have an existing wallet, we need to create one.  As with most of these steps there are multiple ways to do this.  We could use the Wallet Manager GUI but we're going to stick to the command line and use the orapki utilty:

 

Create a new wallet with the auto-login property set:

 

c:\app>orapki wallet create -wallet c:\app\db_wallet -auto_login

 

We're prompted to enter a password to secure the wallet and I've used password1.  The directory listing shows the files created in the db_wallet directory which will be created if it does not already exist.

 

We now have an empty wallet to which we need to add a certificate.  As this is a test we'll create a self-signed certificate and add it with one command:

 

c:\app>orapki wallet add -wallet c:\app\db_wallet -dn "cn=clm-pun-013056,cn=bmc,cn=com" -keysize 2048 -validity 365 -pwd password1 -self_signed

c:\app>orapki wallet display -wallet c:\app\db_wallet -pwd password1

 

We've used the host name for the -dn option, specified a key length of 2048 bits and validity of a year.  The second command lists the contents of the wallet so that we can confirm that our certificate has been added.

 

Note that both user and trusted certificates called CN=clm-pun-013056,CN=bmc,CN=com were created and it is the latter that we will export so that it can be used on the AR System server.

 

Export the certificate to a file called db_CA.cert:

 

c:\app>orapki wallet export -wallet c:\app\db_wallet -dn "cn=clm-pun-013056,cn=bmc,cn=com" -cert c:\app\db_wallet\db_CA.cert -pwd password1

 

We've prepared the certificate but we still need to configure Oracle to use it.  To do this we need to edit two files in the ORACLE_HOME\network\admin directory, sqlnet.ora and listner.ora, and add these lines to both of them:

 

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = C:\app\db_wallet)

    )

  )

SSL_CLIENT_AUTHENTICATION = FALSE

 

This specifies the location of the wallet and sets an option to show we're just using encryption, not authentication.

 

We also need to configure the listener to add a port that the database will use for SSL connections.  In the LISTENER section of the listener.ora file we add:

 

    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = clm-pun-013056)(PORT = 2484)))

 

Note the protocol is TCPS and we've picked port 2484 which is commonly used.

 

Finally we need to restart the listener process so that it picks up the changes:

lsnr.PNG

 

That completes the database setup, the listener output shows we're ready to receive SSL connections on port 2484.

 

The next step is to copy the certificate that we exported earlier to the AR Server system and add it to the Java cacerts file so that the Oracle JDBC driver can use it.  These steps are similar to those we used for MS SQL.  The certificate file is called db_CA.cert and it has been copied to c:\temp.

 

Open a command prompt and cd to the jre\lib\security directory of the Java instance that the AR System server is using.  There should already be a cacerts file in this directory, this is the default certificate store used by Java, and we're going to add our certificate to it with the keytool command:

 

C:\Program Files\Java\jre1.8.0_121\lib\security>..\..\bin\keytool -importcert -file c:\temp\db_CA.cert -alias dbcert       -storepass changeit -noprompt -keystore cacerts

imp.PNG

 

We're almost done, all that is left is to configure the Remedy server to use SSL when connecting to the database.  A typical Remedy server configuration for an Oracle database includes these settings:

 

Db-Host-Name: clm-pun-013056

Db-Server-Port: 1521

Oracle-Service: orcl

 

On startup the server uses these to create a JDBC connection string using the format:

 

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Db-Host-Name)(PORT=Db-Server-Port))(CONNECT_DATA=(SERVICE_NAME=Oracle-Service)))

 

When using SSL the PROTOCOL setting needs to be changed from TCP to TCPS.  However, before 9.1 Service Pack 2, there was no way to modify this connection string to do this.  This release introduced a new configuration option called Oracle-JDBC-URL which can be used to provide the full connect string.  If this option is present it is used instead of the one derived from the settings above.  To configure our Remedy server we need to add this option with the appropriate values.  So, the new setting in our ar.cfg/ar.conf will be:

 

Oracle-JDBC-URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=clm-pun-013056)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=orcl)))

 

The original settings can be left in place as they are ignored when the new option is set.  Switching between SSL and plain text connections is simply a case of commenting out this new option.

 

Restart the AR System server and we now have an encrypted connection between the server and the Oracle database.  To verify that this is the case we can use the tcpdump or Wireshark tools as detailed in the earlier posts.  Looking at the packets we'll see that the contents are all binary data and no plain text is present. 

 

 

Summary

 

We've now looked at three different ways to encrypt data as it is transferred between Remedy and the database.  In each case I've tried to cover the minimum steps required to enable this feature, each one offers many more configuration options, and you can find additional details in the links at the end of the articles.

 

I hope the information is useful and I welcome suggestions for other topics that would be of interest to the Remedy community.  Please use the comments section below or send me a message with ideas.

 

Further Reading

 

Trending in Support: Encrypting Data Between AR Servers and Oracle Databases

Trending in Support: Enabling SSL Encryption for AR to MS SQL Database Connections with Remedy 9.1 SP2 and Later

 

SSL With Oracle JDBC Thin Driver

http://chadstechnoworks.com/wptech/db/oracle_advanced_security_p02.htmlOracle Advanced Security Configuration

HOW TO: Setting up Encrypted Communications Channels in Oracle Database

 

Feedback and corrections are always welcome in the comments section below.

 

Mark Walters

 

Read more like this -  BMC Remedy Support Blogs