Support for TLS v1.2 (Microsoft SQL Server)

CA UIM supports Transport Layer Security (TLS) v1.2 when communicating with the CA UIM database: Microsoft SQL Server. This support enables the UIM Server to establish secure communication with the UIM database. To enable TLS v1.2 support for Microsoft SQL Server, ensure that you perform the required configurations on the Microsoft SQL Server computer (database server) and UIM Server (client computer).
uim902
CA UIM supports Transport Layer Security (TLS) v1.2 when communicating with the UIM database: Microsoft SQL Server. This support enables the UIM Server to establish secure communication with the UIM database. To enable TLS v1.2 support for Microsoft SQL Server, ensure that you perform the required configurations on the Microsoft SQL Server computer (database server) and UIM Server (client computer).
The following Microsoft SQL Server versions are supported:
  • 2012
  • 2014
  • 2016
  • 2017
  • 2019
The following diagram shows the high-level process:
Microsoft SQL Server TLS 1.2 Support
Microsoft SQL Server TLS 1.2 Support
  • CABI is not supported for Microsoft SQL Server 2017.
  • The cabi 4.30 probe supports TLS v1.2 except if Microsoft SQL Server 2012, 2014, or 2016 is installed on Windows Server 2016.
  • The cabi 4.10 probe supports TLS v1.2 when communicating with the UIM database: Microsoft SQL Server 2012, 2014, and 2016. However, CABI is not supported if Microsoft SQL Server 2012, 2014, or 2016 is installed on Windows Server 2016 and TLS v1.2 is enabled.
  • The cabi 3.40 probe, available with UMP 9.0.2 HF2, supports TLS v1.2 when communicating with the UIM database: Microsoft SQL Server 2012 and 2014. However, CABI is not supported if Microsoft SQL Server 2012 or 2014 is installed on Windows Server 2016 and TLS v1.2 is enabled.
    For more information about how to apply the UMP 9.0.2 HF2 for CABI TLS functionality, see UMP 9.0.2 HF2.
  • The cabi 3.32 probe does not support TLS v1.2 when communicating with the UIM database: Microsoft SQL Server 2012, 2014, 2016, and 2017. As a result, you cannot view the Operator Console home page, OOTB CABI dashboards, and OOTB CABI reports.
  • TLS v1.2 support is not enabled by default when you install CA UIM 9.0.2.
Configurations on Database Server
Perform the following tasks on the database server.
  1. Verify FQDN Requirement
  2. Verify and Apply Patches for Microsoft SQL Server
  3. Disable Previous Versions of Certificates
  4. Import the Certificate to Database Server 
  5. Grant SQL Server Rights to Use the Certificate
  6. Enable Encryption on Database Server
  7. Export the Certificate on Database Server
Verify FQDN Requirement
Verify that your full computer name is FQDN (for example, VI02-E74.ca.com). If not, add the domain name (for example, ca.com) to the computer name.
Follow these steps:
  1. Access the properties panel of your computer (for example, right-click the Computer icon on your desktop and select
    Properties
    ).
  2. Click
    Advanced system settings
    in the left pane.
  3. Click the
    Computer Name
    tab.
  4. Click
    Change
    .
  5. Click
    More
    .
  6. Enter your domain name in the
    Primary DNS suffix of this computer
    field.
  7. Click
    OK
    and restart the computer.
  8. Verify that your full computer name is now FQDN.
The following example screenshot shows that the full computer name is FQDN:
FQDN.jpg
Verify and Apply Patches for Microsoft SQL Server
For Microsoft SQL Server versions that do not provide support for TLS v1.2 by default, follow the information in the article TLS 1.2 support for Microsoft SQL Server. By following the instructions in this article, you can download and apply the required packages depending on your Microsoft SQL Server version. For Microsoft SQL Server versions (for example, 2016) that support TLS v1.2 by default, you do not need to perform this manual process.
Disable Previous Versions of Certificates
Change the registry keys to disable all the previous versions of certificates on the database server. Verify the following registry keys on the database server:
  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2
  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server
For the Client and Server entries, enter the following DWord and Value entries:
  • DisabledByDefault=00000000
  • Enabled=00000001
For more information, see the TLS 1.2 section on TLS/SSL Settings.
Import the Certificate to Database Server
(For certification authority-approved certificates) Use Internet Information Services (IIS) to import the CA-approved certificate to the database server. Ensure that you have the required certificate available with you.
Install IIS on the database server if it is not already installed.
Follow these steps:
  1. Click Start, Run, and enter inetmgr to open IIS.
  2. Click
    <server_name>
    .
  3. Locate and double-click
    Server Certificates
    as shown in the following example screenshot:
    IIS.jpg
  4. Right-click in the right pane and select
    Import
    from the context menu.
    The
    Import Certificate
    dialog opens.
  5. Navigate to the location where your certificate file is available.
  6. Enter the required password.
  7. Click
    OK
    .
The certificate is imported to the database server. The following example screenshot shows an imported certificate:
Certificate.jpg
When using certificates, the certificate must be issued to FQDN (Fully Qualified Domain Name) of the computer, not the host name. Also, ensure that the database server name must also be FQDN. If both the certificate and the server name are not FQDN, you will encounter connection issues.
The import procedure explained above is not required for self-signed certificates. When you create self-signed certificates using IIS, they become available in IIS. Therefore, you do not need to perform this import process.
Create Self-Signed Certificates Using IIS
Review the following steps if you want to create a self-signed certificate using IIS:
  1. Verify that your full computer name is FQDN (for example, sa-01.ca.com). If not, follow the steps that are mentioned in the System Requirements section.
  2. Click Start, Run, and enter inetmgr to open IIS.
  3. Click
    <server_name>
    .
  4. Locate and double-click
    Server Certificates
    .
  5. Right-click in the right pane and select Create Self-Signed Certificate from the context menu.
  6. Enter the FQDN name (for example,
    <computer_name>
    .ca.com) for the certificate.
  7. Click
    OK
    .
The self-signed certificate is created and is listed in the
Server Certificates
pane.
Grant SQL Server Rights to Use the Certificate
You must provide the SQL Server rights to use the certificate. You use SQL Server Configuration Manager and Microsoft Management Console to perform this task.
Follow these steps:
  1. Open SQL Server Configuration Manager.
  2. Locate and select
    SQL Server Services
    in the left pane.
  3. Select your SQL Server instance in the right pane.
  4. Right-click SQL Server instance and select
    Properties
    from the context menu as shown in the following screenshot:
    SQLServerAccess.jpg
  5. Copy the account name entry present in the
    Account Name
    field.
  6. Open the Microsoft Management Console (MMC).
  7. Click
    File, Add/Remove Snap-in
    .
  8. Click
    Certificates
    .
  9. Click
    Add
    as shown in the following example screenshot:
    MMC_Access.jpg
  10. Select
    Computer account
    .
  11. Click
    Next
    .
  12. Select the local computer option.
  13. Click
    Finish
    .
  14. Click
    OK
    .
  15. Locate and select the certificate.
  16. Right-click the certificate, select
    All Tasks, Manage Private Keys
    from the context menu.
  17. Add the copied account name.
  18. Grant the Read access to the account name.
Enable Encryption on Database Server
Use the SQL Server Configuration Manager to enable the encryption on the database server.
Follow these steps:
  1. Open SQL Server Configuration Manager.
  2. Locate and expand
    SQL Server Network Configuration
    .
  3. Right-click on
    Protocols for
    <SQL_Server>
    and select
    Properties
    from the context menu as shown in the following example screenshot:
    Enable_Encryption_DB.jpg
  4. Click the
    Certificate
    tab.
  5. Select the required certificate from the
    Certificate
    drop-down list.
  6. Click the
    Flags
    tab.
  7. Select
    Yes
    for the
    Forced Encryption
    option as shown in the following example screenshot:
    Forced Encryption.jpg
  8. Click
    OK
    .
  9. Restart the SQL Server service.
The encryption is enabled on the database server for the certificate.
Export the Certificate from Database Server
(For self-signed certificates) Export the self-signed certificate to the database server so that the UIM Server (client in this case) can use it. The UIM Server (client) must trust the certificate that is available on the database server.
You do not need to perform this task in case of CA-approved certificates because the certificate file is already available.
Follow these steps:
  1. Open the Microsoft Management Console (MMC).
  2. Click
    File, Add/Remove Snap-in
    .
  3. Click
    Certificates
    .
  4. Click
    Add
    .
  5. Select
    Computer account
    .
  6. Click
    Next
    .
  7. Select the local computer option.
  8. Click
    Finish
    .
  9. Click
    OK
    .
  10. Locate the certificate.
  11. Right-click the certificate and select
    All Tasks, Export
    from the context menu as shown in the following screenshot:
    All_Tasks_Export.jpg
  12. Click
    Next
    on the Certificate Export Wizard.
  13. Follow the required selections for
    Base-64 encoded X.509 (.CER)
    and specify the location where you want to save the exported file. The location must be accessible to the UIM Server (client computer).
The self-signed certificate is successfully exported to a location on the database server that is accessible to the UIM Server.
You have successfully configured your UIM database server for the TLS v1.2 support.
Configurations on UIM Server
Perform the following tasks on the client (UIM Server).
  1. Import the Certificate on UIM Server
  2. Create Java KeyStore for Server Certificate
  3. Install UIM Server
Import the Certificate on UIM Server
Import the certificate on the UIM Server (client computer). This step is required to ensure that the UIM Server can trust the certificate that is available on the database server. You must import the certificate into the Trusted Root Certification Authorities certificate store on the UIM Server.
Follow these steps:
  1. Open the Microsoft Management Console (MMC).
  2. Click
    File, Add/Remove Snap-in
    .
  3. Select
    Certificates
    and click
    Add
    .
  4. Select
    Computer account
    .
  5. Select the local computer option.
  6. Click
    Finish
    .
  7. Click
    OK
    .
  8. Click
    Certificates (Local Computer)
    .
  9. Navigate to the
    Trusted Root Certification Authorities
    folder.
  10. Right-click the
    Trusted Root Certification Authorities
    folder and select
    All Tasks, Import
    from the context menu as shown in the following screenshot:
    All_Tasks_Import.jpg
  11. Click
    Next
    on the Certificate Import Wizard.
  12. Click
    Browse
    and navigate to the location where you saved the certificate file.
  13. Click
    Next
    .
  14. Verify that
    Trusted Root Certification Authorities
    is selected as the place to store all certificates.
  15. Click
    Finish
    .
  16. Click
    OK
    .
The certificate is imported on the UIM Server (client computer) as a trusted certificate.
You must also import the certificate onto the robot where CABI is available. After the import, deactivate and activate CABI.
Create a .jks File for Server Certificate
You also need to create a .jks file (Java keystore file) on the UIM Server to store the server certificate. The .jks file, when created, includes your database server certificate. You can use Java keytool, which is a key and certificate management tool, to generate your .jks file. The tool stores the keys and certificates in a store called keystore.
You specify the location of the generated .jks file during the UIM Server installation. The UIM Server installer copies the .jks file from the specified location and places it in the <Nimsoft>\security folder during the installation. The installer then renames the copied file to truststore.jks.
Follow these steps:
  1. Ensure that JRE (jre1.8.0) is installed on the computer.
  2. Specify the JRE location in the
    PATH
    environment variable; for example,
    C:\Program Files\Java\jre1.8.0_131\bin;
  3. Run the following command using the .cer certificate to generate the .jks file:
    Syntax:
    keytool -import -alias <alias_name> -file <certificate_file> -keystore <jks_filename> -storepass <password>
    Example:
    C:\keytool -import -alias sa-01.ca.com -file sa-01.ca.com.cer -keystore sa-01.ca.com.jks -storepass [email protected]
  4. Enter
    yes
    when prompted whether you want to trust the certificate.
    The .jks file is created.
This command uses the following options:
  • -file
    Specifies the location where the source certificate file is available.
  • -keystore
    Specifies the location where you want to save the .jks file that gets created when the command is executed successfully.
  • -storepass
    Specifies the password for the .jks file.
  • -alias
    Specifies the alias name, which is the database server name (FQDN) in this case.
If your certification authority (CA) provides you a .p12 file, you can use the following command to import it into the .jks file:
Syntax:
keytool -importkeystore -srckeystore <certificate_filename> -srcstoretype <type> -srcstorepass <password> -destkeystore <jks_filename> -deststorepass <password> -alias <alias_name>
Example:
C:\keytool -importkeystore -srckeystore sa01-i185.ca.com.p12 -srcstoretype PKCS12 -srcstorepass [email protected] -destkeystore sa01-i185.ca.com.jks -deststorepass [email protected] -alias sa01-i185.ca.com
The command uses the following options:
  • -srckeystore
    Specifies the location where the self-signed or CA-approved certificate file is available.
  • -srcstoretype
    Specifies the source type.
  • -srcstorepass
    Specifies the password that is associated with the source certificate file.
  • -destkeystore
    Specifies the location where you want to save the .jks file that gets created when the command is executed successfully.
  • -deststorepass
    Specifies the password for the .jks file.
  • -alias
    Specifies the alias name, which is the database server name (FQDN) in this case.
  • Certificate name and database server name must be FQDN.
  • Before you deploy CABI External version 3.4 on a secondary robot, copy the Java keystore file (truststore.jks) file from the UIM Server (<Nimsoft>\security) to the CABI External secondary robot (<Nimsoft>\security).
Install UIM Server
After you perform all the tasks that are listed in this section, review the other pre-installation planning tasks. You can then start the UIM Server installation. During the installation, ensure that you enable the TLS v1.2 option and provide the required information. The UIM Server installer automatically installs the required driver (SQLNCLI11) on the computer during the installation. Also, for the .jks file, browse to the location where you have created the .jks file. The installer copies that file to the
<Nimsoft>\security
folder as truststore.jks.
For more information about the UIM Server installation, see Install UIM Server and Installation Parameters. The following screenshot shows the TLS v1.2 options (
Enable TLS
,
Trust Store Path
, and
TrustStore Password
) during the UIM Server installation:
TLS options in installer.jpg
The TLS v1.2-related options are as follows:
  • Enable TLS:
    Select the option to enable TLS v1.2 in CA UIM, which lets the UIM Server establish a secure communication with the UIM database (Microsoft SQL Server in this case).
  • Trust Store Path:
    Specify the location of the generated .jks file. The UIM Server installer copies the .jks file from the specified location and places it in the
    <Nimsoft>\security
    folder during the installation. The installer then renames the copied file to
    truststore.jks
    . This file includes your database server certificate.
  • TrustStore Password:
    Specify the password to access the source .jks file.
You have successfully enabled the TLS v1.2 support, which allows secure communication with the UIM database (Microsoft SQL Server).
Additional Information
Review the following additional information:
  • In upgrade scenarios and in situations where you want to enable TLS v1.2 support after the UIM Server installation, perform the following tasks on the UIM Server:
    1. Verify and install the required driver (SQLNCLI11), if necessary.
      For more information, follow the information in the "Client component downloads" section in the article TLS 1.2 support for Microsoft SQL Server.
    2. Import the server certificate as a trusted certificate.
    3. Create the Java KeyStore.
    4. Use the data_engine Admin Console or Infrastructure Manager to configure the TLS v1.2-related parameters. When specifying the .jks file location, browse to the location where you have created the .jks file. When you click
      Apply
      or
      OK
      , the .jks file is copied to the
      <Nimsoft>\security
      folder as truststore.jks. This location is then displayed in the
      Trust Store File (.jks)
      field. When you click the
      Test Connection
      option, CA UIM does not verify the validity of the specified .jks file. Instead, it verifies the validity of the certificate that you have imported into the Microsoft Management Console (MMC) on the UIM Server.
      After specifying the options, restart the data_engine probe. The data_engine probe is successfully configured to support TLS v1.2. You can now deploy other probes and use the secure communication when interacting with the UIM database (Microsoft SQL Server). Also, review the impacted probes and packages list. These items have been updated to support TLS v1.2. Ensure that you use the latest version of these items if you want them to work in the TLS v1.2 environment.
      Ensure that the ppm probe version is 3.48 or later and robot version is 7.96 or later to display TLS v1.2 configuration options in Admin Console. Otherwise, TLS v1.2 options are not displayed in Admin Console.
      The following screenshot shows the TLS v1.2 configuration options (
      Enable TLS
      ,
      Trust Store File (.jks)
      ,
      Trust Store Password
      ,
      Always Trust Server Certificate
      ) in Admin Console:
      SQL_Server_AC_TLS_Options.jpg
  • For upgrade scenarios, the CA UIM system can be either TLS v1.2 enabled or disabled for all components; it cannot be a partial TLS v1.2-enabled system. That is, all the infrastructure components across layers (for example, primary hub, secondary hub, probes) should be upgraded to TLS v1.2-supported version.
  • You can enable or disable the TLS v1.2 mode by configuring the data_engine UI. Also, restart of data_engine is needed whenever TLS v1.2 mode is changed.
  • If you upgrade from a previous version of CA UIM to this version, the state of the system remains in non-TLS v1.2 mode. To enable TLS v1.2 mode, perform all the required manual steps that are mentioned above and use the data_engine UI to enable TLS v1.2.
  • When you want to update a certificate (for example, older certificate has expired), create a new .jks file and specify the location of the .jks file and its password in the data_engine UI. The data_engine probe uses that information to create the truststore.jks file in the same
    <Nimsoft>\security
    folder.
  • To run probes that can work on remote computers (other than the primary hub) in TLS v1.2 environment, install the required driver (SQLNCLI11) on the remote computers. For more information, follow the information in the "Client component downloads" section in the article TLS 1.2 support for Microsoft SQL Server.
  • If you encounter any database-connectivity issue in a TLS v1.2-enabled environment, the most probable reason for this issue might be that your certificate is not using FQDN.
Probes and Packages Updated for TLS v1.2
TLS v1.2 related updates have been made to the following items so that they can work in a TLS v1.2 environment. These are the minimum versions with the TLS v1.2 related updates.
  • ace 9.03
  • alarm_routing_service 10.20
  • apmgtw 3.20
  • audit 9.03
  • axagateway 1.32
  • cisco_ucm 2.00
  • cm_data_import 9.02
  • data_engine 9.02
  • discovery_agent 9.02
  • discovery_server 9.02
  • ems 10.20
  • hub 7.96
  • maintenance_mode 9.02
  • mon_config_service 9.02
  • mpse 9.03
  • nas 9.03
  • nis_server 9.03
  • qos_processor 9.02
  • robot 7.96
  • sla_engine 9.02
  • telemetry 1.20
  • trellis 9.02
  • udm_manager 9.02
  • usage_metering 9.11
  • wasp 9.02
  • webservices_rest 9.02
Troubleshooting
The following topics help you troubleshoot a few TLS v1.2-related issues:
data_engine Fails to Start When TLS v1.2 is Enabled
Symptom:
When I try to start data_engine after enabling the TLS v1.2 mode, I get the following connection error:
May 1 10:10:21:897 [4068] 0 de: [main] Open - 3 errors
May 1 10:10:21:897 [4068] 0 de: (1) Open [Microsoft SQL Server Native Client 11.0] Invalid connection string attribute
May 1 10:10:21:897 [4068] 0 de: (2) Open [Microsoft SQL Server Native Client 11.0] SSL Provider: The target principal name is incorrect.
May 1 10:10:21:897 [4068] 0 de: (3) Open [Microsoft SQL Server Native Client 11.0] Client unable to establish connection
May 1 10:10:21:897 [4068] 0 de: COM Error [0x80004005] Unspecified error - [Microsoft SQL Server Native Client 11.0] Invalid connection string attribute
May 1 10:10:21:897 [4068] 1 de: Database script - processing 3 database scripts
How can I address this issue?
Solution:
Your data source uses FQDN for connecting to the database server in the data_engine configuration, but your certificate is not created with FQDN. In such scenarios, the certificate validation fails. Ensure that both the database server name and the certificate use FQDN.
Self-Signed SSL Certificate for Microsoft SQL Server Fails to Validate
Symptom:
I used self-signed certificate the local KeyStore, but I received the following error:
2018-04-30 15:12:15,379 ERROR
dbconfig.UIMServerDatabaseConfigBaseParamsPanel:processTestDBAccess:152 [AWT-EventQueue-0] -
Failed to connect to database server with provided field values. Recheck fields for accuracy.
The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer
(SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server
name in a certificate during Secure Sockets Layer (SSL) initialization.".
ClientConnectionId:89ef826a-2460-4faa-a1a8-d8aba2fc28f2 (501) , Failed to connect to database
server with provided field values. Recheck fields for accuracy.
How can I resolve this issue?
Solution:
This issue is the same as described in the first troubleshooting topic "data_engine Fails to Start When TLS v1.2 is Enabled". Therefore, follow the same solution of ensuring that the database server name and the certificate use FQDN.