Troubleshoot CUIC Error "Invalid sub-protocol Invalid sub-protocol: sqlserver"

Introduction

This document describes how to troubleshoot Cisco Unified Intelligence Center(CUIC) when is not able to connect to the Contact Center Enterprise (CCE) Administration and Database Server (ADS).

Backgound Information

CUIC connects to the awdb on the ADS server. When the connection fails with a specific error and you have collected the CUIC logs you see this error.

0000177816: 10.10.10.10: Jul 14 2018 00:18:21.816 -0700: %CCBU___________CUIC-6-INFO: Checking if it's ok to switch the DS nodes based on exception thrown: Invalid sub-protocol Invalid sub-protocol: 'sqlserver'
java.sql.SQLException: Invalid sub-protocol Invalid sub-protocol: 'sqlserver'
at com.informix.util.IfxErrMsg.getLocSQLException(IfxErrMsg.java:493)
at com.informix.jdbc.IfxDriver.checkURL(IfxDriver.java:560)
at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:208)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)

This is a generic error that indicates that accessing the SQL server data source failed. One common reason for this error relates to a failure of the Transport Layer Security (TLS) handshake process between CUIC and the ADS server.

Problem

When CUIC is not able to connect to the Database and therefore no information is presented when a report is generated,  under CUIC main page > Data Sources,  you see the datasource offline (Red X) as shown in the image.

When the data source is edited, If theTest connect button is selected, you see this error, as shown in the image.

This is the specific error reported: "For database host UCCE.cc.lab Invalid sub-protocol Invalid sub-protocol: 'sql server' " as shown in the image.

Troubleshoot

Step 1. Collect CUIC application logs.

Step 2. In the CUIC application logs you find the exception related to this issue.

0000177816: 10.10.10.10: Jul 14 2018 00:18:21.816 -0700: %CCBU___________CUIC-6-INFO:  Checking if it's ok to switch the DS nodes based on exception thrown: Invalid sub-protocol Invalid sub-protocol: 'sqlserver'
java.sql.SQLException: Invalid sub-protocol Invalid sub-protocol: 'sqlserver'
        at com.informix.util.IfxErrMsg.getLocSQLException(IfxErrMsg.java:493)
        at com.informix.jdbc.IfxDriver.checkURL(IfxDriver.java:560)
        at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:208)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
     

Note: UCCE 11.6 supports Transport Layer Security (TLS) 1.2 for connection to ADS. This error is related to TLS version mismatch as CUIC 11.5 only supports TLS 1.0/1.1. Reference the  Compatibility matrix  for more information.

Summary

The error in this scenario is due to the fact that CUIC 11.5 uses TLS 1.0, while the version used by Microsoft SQL Server is TLS 1.2. 

There is a enhancement defect related to this TLS compatibility issue, where this behavior is described in more detail: CSCvd35048 .

Solution

The error in the logs only indicates connection to the data source failed for any number of reasons. If the issue is related to a TLS negotiation failure, there are two options that can be implemented to solve this problem.

Option 1. CUIC 11.6 supports TLS 1.2 by default. Upgrade the CUIC server to 11.6 and proactively install the latest COP file.This is the recommended option.

Option 2. A temporary workaround can also be used.  You can change some registry keys on the ADS in order to work with TLS 1.0. These are the registry keys that need to be changed.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server] "Enabled"=dword:00000001(Defaults to 0 and disables by default)

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] "Enabled"=dword:00000001(Defaults to 0 and disables by default)

Caution: Option 2 is not recommended. Only use it if you are not be able to upgrade CUIC to version 11.6.