Regular news coverage of data security breaches has made organisations increasingly aware of the importance of securing the data they own and manage. As a result, one question that we're beginning to see more often in support is "How do I encrypt the data travelling between my AR server and database?". The two databases supported by Remedy 9.x servers are Microsoft SQL and Oracle and both have options to provide this type of encryption. This post covers one way to do this with Oracle; a later post will look at an alternative for this database and Microsoft SQL.
The architecture of a basic AR System installation looks something like this;
Data has multiple steps to take as it travels back and forth between clients and the storage medium used by the database server. There are options available to encrypt that data during all of the steps but the one we’re focusing on in this post is highlighted in red on the diagram, the step between the AR System Server and an Oracle database. Often these two servers are on separate machines so the data has to travel over a network and, by default, this transfer takes place in plain text.
To confirm that the data is being passed this way, and that after encryption has been enabled it is no longer in plain text, we’re using a test environment with a version 9.1 AR System Server running on Linux connecting to an Oracle 11g database running on Windows 2012. We will monitor the network traffic travelling the AR and database servers to see what it looks like before and after the changes to turn on encryption.
Logging on to the Linux system we can use one of the many tools available to capture and display network traffic - in this case it’s tcpdump. The command below will display the traffic flowing between the AR and Oracle servers in this environment.
To generate some traffic between the systems we use a User Tool client and start looking at records in the User form. As different records are selected the tcpdump output shows the data being retrieved from the database.
As we can see there is information in the network traffic that can be read. The screen shot above shows the data for user the Allen, including the full name, email address and a list of group IDs/groups.
If the tcpdump command is left running other legible data will be seen. SQL statements for example;
Oracle offers both native and SSL options for encrypting the data between a client and the database server, details are available in many places on the web, one such example is here - ORACLE-BASE - Native Network Encryption for Database Connections.
We’re going to use the native option as it does not require any changes to the client, simply some configuration settings on the database server. The process for enabling this type of encryption is documented here - Configuring Network Data Encryption and Integrity for Oracle Servers and Clients.
One way to make the changes is to edit the Oracle sqlnet.ora file using a text editor but we’re going to use the Net Manager utility that is installed as part of the database software.
On the database server system launch the Net Manager tool and click on Profile in the tree window. Select Oracle Advanced Security in the drop down menu and then the Encryption tab.
This is where the various encryption options are selected. They are all covered in the link above and for this test we use these settings;
Encryption Type: requested
Encryption Seed: secretword
Selected Methods: AES256
Select Save Network Configuration from the File menu and quit Net Manager.
We have now enabled encryption on the database server. The options we have set request that encryption be enabled if the client supports it and we have specified a seed and algorithm to be used.
If we now go back and repeat the tcpdump test above what do we see? When we select another user record, Bob's for example;
That doesn't look good - the data is still visible in plain text. This is because the encryption configuration change is only picked up when a client first connects to the database, so a restart of the AR System Server is required. Once this is done the test is repeated and the network traffic looks a little different;
The data is no longer in plain text – it is encrypted. A positive step forward in an increasingly security conscious world!
I’m not sure how widely known this feature of Oracle is but, as we have shown, with a simple change on the database server and a restart of AR, it is possible to encrypt the traffic between these systems. No changes are necessary on the AR System Server and this should work with any version of AR as it is a feature of the Oracle database and client software.
In a future post I’ll look at how AR to database encryption can be enabled using SSL with both Oracle and Microsoft SQL.
Feedback and corrections are always welcome in the comments section below and, if you have a suggestion for a technical post related to Remedy AR System, please drop me a message via the Communities.
Read more like this - BMC Remedy Support Blogs