The following information pertains to FootPrints Service Core 20.19.01 or later (using JDK). This article describes the configuration changes needed for Footprints Service Core 20.19.01 or later, when using an encrypted connection between FootPrints Service Core 20.19.01 or later and SQL Server.

Overview

1. Gather information about your SQL Server configuration.
2. Adjust Tomcat startup parameters.
3. Add the certificate to the keystore (optional).
4. Edit the FootPrints Service Core configuration.
5. Restart Tomcat.
6. Verify secure connection.

In following the steps below, C:\Program Files\BMC Software\FootPrints Service Core\ refers to the "base install location" of FootPrints Service Core. Please adjust the location of where FootPrints Service Core is actually installed in your environment.
 

1. Gather information about your SQL Server configuration.
   1. Open SQL Server Configuration Manager on the server that hosts SQL Server. Expand the SQL Server Network Configuration item. Right-click on Protocols for MSSQLSERVER, and choose Properties.
   2. On the first tab, Flags, there is an option to Force Encryption. This can be changed to Yes to require that all connections to the server are encrypted. Alternatively, it can be left set to No if there are other applications that use a non-encrypted connection.
   3. On the second tab, Certificate, there is an option to choose a specific certificate. Make note of the chosen certificate. If none is chosen, that means SQL Server will use its own self-signed certificate.
   4. This should be all the information needed from SQL Server. The rest of the steps concern the FootPrints Service Core server.
2. Adjust Tomcat startup parameters
   1. Navigate to your Tomcat installation’s bin folder (C:\Program Files\Apache Software Foundation\Tomcat 8.5\bin, by default). If your Tomcat is installed in a different location, navigate there.
   2. Run the Tomcat8w.exe application.
   3. Click on the Java tab.
   4. Edit the Java options, adding the following line to the bottom of the list of options. Make sure to enter it on its own line.

      -Djsse.enableCBCProtection=false

   5. Click Apply and then OK.
3. Add the certificate to the keystore.

   This step is optional. Depending on what certificate your SQL Server is using, it may not be applicable. With or without this step, all communication with the SQL Server will be encrypted. The benefit of this additional step is that it also forces FootPrints Service Core to verify the identity of the SQL Server.

   Per Step 1.c. above, you made note of which certificate is being used by SQL Server (on the certificate tab in the SQL Server configuration).
   • If no certificate is selected: This means SQL Server is using its own self-signed certificate. This step does not apply.
   • If the certificate came from a well-known trusted certificate authority (e.g. Verisign, Thawte), this step can most likely be skipped. Tomcat has a built-in set of trusted certificates from many CAs.
   • If a self-signed certificate is being used, or a certificate signed by a local CA, follow this step, to let Tomcat know that it can trust this certificate.
   1. Obtain a copy of the certificate that SQL Server is using, or the CA public certificate. This will be a .pem, .cer, or .pfx file. The examples below will use the filename mycert.cer.
   2. Open a command prompt and run the following command (INCLUDE the double quote characters which are needed due to the spaces included in the path), adjust JDK path as per your environment:

      "C:\Program Files\Java\jdk-12.0.2\bin\keytool" -importcert -alias mycert -keystore "C:\Program Files\Java\jdk-12.0.2\lib\security\cacerts" -storepass changeit -file C:\mycert.cer

   3. As an alternative to the keytool.exe command, an easier-to-use key management tool can be found at http://portecle.sourceforge.net/. If an error occurs using keytool, or if keytool does not recognize the certificate format, this tool is recommended.
      1. Download it from https://sourceforge.net/projects/portecle/
      2. Unzip the file, for example into C:\portecle-1.7.
      3. Run the following command (again, INCLUDE the doublequote characters):

         "C:\Program Files\Java\jdk-12.0.2\bin\java" -jar C:\portecle-1.7\portecle.jar

      4. Choose File | Open CA Certs Keystore.
      5. The password is "changeit".
      6. Choose Tools | Import Trusted Certificate.
      7. Select your certificate file.
      8. Click OK at the "Could not establish a trust path …" prompt.
      9. Click OK on the certificate details.
      10. Click Yes when prompted to trust the certificate.
      11. The certificate can be given an alias, or just accept the default value.
      12. Choose File | Save Keystore.
4. Edit the FootPrints Service Core configuration.
   1. Open the C:\Program Files\BMC Software\FootPrints Service Core\conf\footprints-environment.properties file in notepad or other text editor such as WordPad, EditPlus, etc.
   2. Find the following line ("sqlserver" would be replaced with your actual Database server name):

      NOTE: If your sql server name (shown as "sqlserver" in the line below) is specified as "localhost", it is advisable to change it to the actual server name, in order to match the server name specified in the certificate.

      DatabaseConnectionUrl=jdbc:jtds:sqlserver://sql-server-name:1433/fpscdb001

   3. At the end of the line, insert the text ";ssl=require". The line should now look as follows:

      DatabaseConnectionUrl=jdbc:jtds:sqlserver://sql-server-name:1433/fpscdb001;ssl=require

      Alternatively, if it is desired to force verification of the SQL Server’s identity, use the text "ssl=authenticate;". (This goes together with the previous step of importing the certificate into the keystore: Tomcat needs to trust the certificate for this to work). In this case, the line would look as follows:

      DatabaseConnectionUrl=jdbc:jtds:sqlserver://sql-server-name:1433/fpscdb001;ssl=authenticate

   4. Save the footprints-environment.properties file.
5. Restart Tomcat
   1. Access Start | Administrative Tools | Services
   2. Right-click the Apache Tomcat 8.5 Tomcat8 service and choose Restart.
6. Verify Secure Connection
   If the SQL Server is configured to force encryption for all connections, simply log in to FootPrints Service Core to confirm that the secure connection is working.
   Or, if the SQL Server allows some secure and some non-secure connections, the following steps can be used to verify that FootPrints is using a secure connection.
   1. Log in to FootPrints Service Core.
   2. Open SQL Server Management Studio and connect to the database server housing the FootPrints database and run this query:

      exec sp_who2;

   3. Find the result rows with:

      DBName = fpscdb001 and ProgramName = jTDS.

      Note the SPID values from those rows.
   4. Run this query:

      SELECT session_id,encrypt_option FROM sys.dm_exec_connections;

   5. Confirm that the rows with the SPIDs noted above have encrypt_option=TRUE.

Additional Notes

The remaining information in this article contains troubleshooting and debugging information and steps for creating a "test" certificate (normally used for non-production, internal testing).

Creating a test certificate

In most cases, it is expected a certificate will be obtained from a trusted CA (e.g. VeriSign, Thawte), or SQL Server's self-signed certificate may be used, in which case the steps below do not apply. If there is a need to create a test certificate for SQL Server, here are steps that can be followed:

1. Obtain OpenSSL from http://slproweb.com/products/Win32OpenSSL.html. The page that opens also contains a link for the 64 bit version.
2. Put the following in a text file named C:\certs\cert3_config.txt. The CN must match your server's name. Change other values (such as "NJ" for ST, "Randolph" for L as needed for your environment):

distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
C = US
ST = NJ
L = Randolph
O = FPSQL
OU = BMC
CN = sql-server-name
[v3_req]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth

3. Run the following command to create the certificate:

   C:\OpenSSL-Win64\bin\openssl.exe req -x509 -nodes -days 730 -newkey rsa:2048 -keyout C:\certs\cert3.pem -out C:\certs\cert3.pem -config C:\certs\cert3_config.txt

4. Run the following command to create the PFX file (the example password "MYcert123" can be changed if desired):

   c:\OpenSSL-Win64\bin\openssl.exe pkcs12 -export -out C:\certs\cert3.pfx -in C:\certs\cert3.pem -name "cert3 (SQLCert)" -passout pass:MYcert123

5. Import the certificate on the SQL Server, first to Personal and then to Trusted Root Certification Authorities. See https://msdn.microsoft.com/en-us/library/ms191192.aspx for details.
6. Update file and registry permissions so that SQL Server can see the new certificate:
   1. Add full control for these folders, for the user that runs the SQL Server service:

      C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
      C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys

   2. Add full control to this registry key, for the user that runs SQL Server:

      HKLM\System\CurrentControlSet\Services\WinSock2\Parameters

   3. Restart SQL Server service.

This new certificate should now be seen in the dropdown list when choosing a certificate in SQL Server Configuration Manager (i.e. step 1.c. above).