This post shows how to use a new configuration option, added in AR System Server 9.1 Service Pack 2, to enable encryption of the data moving between a Remedy server and its Microsoft SQL Server database. In an earlier post (Trending in Support: Encrypting Data Between AR Servers and Oracle Databases ) we saw how to enable Oracle's native encryption for these connections but, this time, we're going to be using SSL. Microsoft have documentation on their website that describes how the feature is implemented.
There are several steps necessary to prepare the environment before encryption can be enabled. At a high level these are:
- obtain a certificate
- grant SQL Server access to the certificate
- configure SQL to use the certificate
- import the public certificate to the Java instance used by the AR System server
- enable encryption on the AR System server
If you're configuring a production environment that requires this additional level of security you have probably already obtained an SSL certificate from one of the available commercial certification authorities. However, for our tests, we're going to use a simple, self-signed, certificate. There are a number of different ways to generate these but, as we happen to have IIS installed on our SQL Server machine, we'll use that. Simply start the IIS Manager, goto Server Certificates and right click Create Self-Signed Certificate:
Enter a name and choose a Personal certificate.
Now that we have a certificate we need to make it available to our SQL Server instance. Start by finding the account name used to run SQL. One way to do this is via the SQL Server Configuration Manager, check the Properties for the selected instance:
Note the Account Name and then launch the MMC management console and add the Certificates snap-in for a Computer Account:
in MMC, go to Certificates (Local Computer) > Personal > Certificates
the certificate should be listed there (you may have to import it if you did not use IIS to create it)
right click > All Tasks > Manage Private Keys
add the service account for your instance of SQL Server
give the service account Read permissions
While we're here we also need to export the certificate so that it can be imported on the AR System server machine later:
- right click on the certificate > All Tasks > Export > Next
- choose No, do not export the private key > Next
- choose DER encoded binary X.509 (.CER) > Next
- enter a file name (e.g. export.cer) noting where it is saved
The final step on the SQL Server machine is to configure SQL to use the certificate with the SQL Server Configuration Manager again:
- start SQL Server Configuration Manager
- go to SQL Server Network configuration
- select your instance
- right click > Properties > Certificate tab
- choose the certificate from the list
- restart the SQL service
We're finished with the SQL Server machine, the rest of the work is done on the AR System server host.
Start by copying the exported certificate file (which we called export.cer) created above to the system. Then, open a command prompt and cd to the jre\lib\security directory of the Java instance that you are using to run your AR System server.
There should already be a cacerts file in this directory, this is a default certificate store used by Java, and we're going to add our certificate to it with the keytool command.
With the commands shown above we:
- imported the certificate with an alias of arkey using the default store password of changeit
- listed the certificate to verify it was imported
The final step is to enable the AR System server to use the certificate and encrypt traffic between itself and the database. To do this we need to make use of a new configuration parameter that was added in 9.1 Service Pack 2 called Db-Custom-Conn-Props: This allows us to pass one or more key=value pairs to the database driver using a semi-colon separated list. For example:
This option was added in 9.1 SP2 to provide a way for administrators to specify the additional configuration options required for the JDBC driver when enabling features such as encryption. We'll make use of it again when we look at SSL for Oracle databases in a future post.
Before we move on let's confirm the current state of the data flowing to and from the database. In the earlier Oracle post we used tcpdump to snoop on the network traffic. We're going to do the same here but with the graphical Wireshark utility. This next picture shows some of the data packets coming from the database and we can see that there is plain text legible in their contents:
The above is some of the data being returned when selecting the User form record for the sample user Allen. The full name and email address are there, along with the start of the list of groups that Allen is a member of.
To enable encryption we need to stop the AR System service add this line to our ar.cfg file;
and then restart the service. We could also have used the Centralised Configuration forms to add this to our server before restarting.
Now, when we look at the Wireshark captured data, we can immediately see a difference:
Note that the Info column is showing TLS traffic and the packet payload data is no longer in plain text - an encrypted connection!
We've deliberately glossed over some of the complexities that may be required in non-test environments such as:
- using commercial SSL certificates
- using alternative Java keystores
- additional Db-Custom-Conn-Props options that may be need for different SSL configurations such as different keystore locations and passwords
but I hope that this shows that, with 9.1 Service Pack 2 and beyond, AR System server to database encryption is now supported when using Microsoft SQL databases.
Thanks to The Data Specialist blog post for details of configuring SQL Server with a self-signed certificate.
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