3 Replies Latest reply on Jan 16, 2018 9:20 AM by Tom Whitfield

    Secure SQL Connection for FP12

    Tom Whitfield

      I have a customer who requires a secure connection with SQL.

       

      BMC has Tech Doc 000114248 which documents how to do this.

       

      Step K of the tech doc states

       

      However, when I try this, I get:

      'build.bat' is not recognized as an internal or external command, operable program or batch file.

       

      Can anyone help me out? I've checked my %PATH% file and it looks exactly like how the doc says it should. I've done a search for "build.bat" on my C:\ drive but there are no results.

      I was assuming this file was part of the JDK, but it's apparently not. I have no problem building a .bat file if that's what's needed, but there are no instructions in the document on how it should be built.

       

      I'm on Windows Server 2012 R2.

        • 1. Re: Secure SQL Connection for FP12
          Joe Cullin

          That is part of the jTDS driver download. Step "e" is a link to sourceforge, to download the driver files. The build.bat file is in the root directory of that download.

          • 2. Re: Secure SQL Connection for FP12
            Tom Whitfield

            Interesting, because it's not in the file I downloaded.

             

            I'm installing on a DoD system that is thoroughly STIG, though, and I'm wondering if there are policies in place that may have stripped out the .bat from the directory.

            • 3. Re: Secure SQL Connection for FP12
              Tom Whitfield

              Just to follow up, the tech doc that BMC has is wrong on this. There is a specific jtds-1.2.8.jar file you must use that BMC can provide.

               

              Here are the actual steps I took to get this to work.

               

              ------------------------------------------------------

               

              1. Gather information from SQL Server.
                1. Open SQL Server Configuration Manager on the SQL Server. Expand SQL Server Network configuration. Right-click on Protocols for MSSQLSERVER. Choose Properties.
                2. On the Flags tab, there is an option to Force Encryption. This can be changed to Yes to require all connections to SQL be encrypted. If this is an instance dedicated to Footprints, select Yes. Otherwise, keep it to No if there are other applications using the server that don’t use encryption.
                3. On the Certificate tab, there is an option to choose a certificate. Make a copy of the certificate used. If there is no certificate, then SQL will use its own self-signed certificate.
              2. Update the JTDS driver
                1. Open …\Program Files\BMC Software\Footprints\web\WEB-INF\lib folder in Windows Explorer.
                2. Make a backup copy of the jtds-1.2.x.jar file, making sure to change its extension.
                3. Replace it with the BMC supplied jtds-1.2.8.jar file.
              3. Adjust Tomcat parameters
                1. Run Tomcat7w.exe
                2. Click on the Java tab
                3. Add the following line to the Java options: -jsse.enableCBCProtection=false
                4. Click Apply, then OK.
              4. Add the certificate to the keystore. Note: This step is optional, depending on what certificate the SQL Server is using. With or without this step, all communication with the SQL Server will be encrypted. The benefit of this step is to force Footprints to verify the identity of the SQL Server.

                Per Step 1.c above, you made note of any certificate used by SQL Server.

                1. If no certificate was selected, then SQL is using its own self-signed certificate and Step 4 does not apply.
                2. If the certificate was from a well-known, trusted Certificate Authority such as Thawte or Verisign, this step can probably be skipped. Tomcat has a built-in set of trusted certificates from many CAs.
                3. If a self-signed certificate is being used, or if the certificate is signed by a local CA, then follow this step so Tomcat can know it can trust the 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 example below will use the filename mycert.cer.
                2. Download the Portecle app from http://portecle.sourceforge.net/projects/portecle.
                  1. Unzip the file into its own folder. In this example, we’ll use C:\portecle-1.7.
                  2. Run the following command, including the double-quote characters. Be sure to point to the version of Java you have installed:
                    “C:\Program Files\Java\jre1.8.0_51\jre\bin\java” –jar C:\portecle-1.7\portecle.jar
                  3. Choose File | Open CA Certs Keystore
                  4. The password is: changeit
                  5. Choose Tools | Import Trusted Certificate
                  6. Select your certificate file
                  7. Click OK at the “Could not establish a trust path…” prompt
                  8. Click OK on the certificate details
                  9. Click Yes when prompted to trust the certificate
                  10. The certificate can be given an alias, or you can accept the default value
                  11. Choose File | Save Keystore

                   5. Edit the Footprints Service Core configuration

                1. Open Program Files\BMC Software\Footprints\conf\footprints-environment.properties in Notepad or another text editor.
                2. Find the following line (“sqlserver” should be replaced with your actual database server name). Note: if your SQL Server name is shown as “localhost”, it should be changed to the actual server name in order to match the server name specified in the certificate:

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


                3. At the end of the line, insert the text “;ssl=require”. The line should now look like this:

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

                4. Alternately, if it’s desired to force verification of the SQL Server’s identity, use “ssl=authenticate” instead, so the line reads as follows:

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

                5. Save the footprints-environment.properties file

               

                        6. Restart Tomcat

               

                        7. Verify Secure Connection. If the SQL Server is configured to force encryption for all connections, simply log in to Footprints to confirm the                secure connection is working. If the SQL Server allows some secure, and some unsecure, connections, the following steps can be used to                verify Footprints is using a secure connection:

                  1. Log into Footprints
                  2. Open SQL Server Management Studio and connect to the database housing the Footprints database and run this query: exec sp_who2;
                  3. Find the result rows with:
                    DBName = fpscdb001
                    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.