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
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.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 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 to 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 and robot version is 7.96 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. 
  • 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
  • fault_correlation_engine 9.03
  • 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
  • relationship_services 9.03
  • robot 7.96
  • sla_engine 9.02
  • telemetry 1.20
  • topology_agent 9.03
  • topology_fault_correlation 9.03 
  • trellis 9.02
  • udm_manager 9.02
  • ump_relationshipviewer 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.