Troubleshooting
Problem
When the SQL Server machine is configured to disable TLS 1.0 and 1.1, only allowing TLS 1.2 or when connecting a SqlServer of version 2016 or higher, Cognos must make a TLS 1.2 connection to it even if SQL Server is not forcing encryption and there is no SSL certificate involved.
Symptom
An example error connecting to the Content Store might be:
CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store.
CM-SYS-5003 Content Manager is unable to connect to the content store. Verify that the database connection properties in the configuration tool are correct and that when you test the connection, the test is successful.
Cause: The driver could not establish a secure connection to SQL Server as SQL Server cryptography require TLS1.2 interaction under such server environment or connection to any SQL Server database of version 2016 or higher.
Error: "SQL Server did not return a response. The connection has been closed.".
Resolving The Problem
To get the Content Store connection working:
- Ensure that the unrestricted jre policy files are added and in cognos configuration you add the SHA256 ciphersuites.
- Use the latest SQL Server jdbc driver (at least 7.0) for the java version that Cognos uses. You can find the latest SQL Server JDBC driver here: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
- Edit install_location\bin64\startwlp.bat (Windows) to add the following lines after the line set JVM_ARGS=-Xmx4096m -XX:MaxNewSize=2048m -XX:NewSize=1024m %DEBUG_OPTS%:
set JVM_ARGS="-Dcom.ibm.jsse2.overrideDefaultTLS=true" %JVM_ARGS%
NOTE: The startwlp.bat file no longer exists in 11.1.5 and higher.
- Edit install_location\bin64\bootstrap_wlp_os_version.xml to add the following lines after the line <param condName="${java_vendor}" condValue="IBM">-Xscmaxaot4m</param>:
<param>"-Dcom.ibm.jsse2.overrideDefaultTLS=true"</param>
- Edit install_location\bin64\cogconfig.bat (Windows) to add the following lines after the line set J_OPTS=%DD_OPTS% %J_OPTS%:
set J_OPTS="-Dcom.ibm.jsse2.overrideDefaultTLS=true" %J_OPTS%
- If SQL Server has 'Force Encryption' set to 'Yes' (meaning that it is using an SSL certificate), you will need to import the SQL Server certificate into the Java cacerts keystore for your JRE. For example (using the Cognos JRE):
- Obtain the root Certificate Authority certificate that issued your SQL Server's certificate (or the self-signed server certificate if it was not issued by a Certificate Authority), and copy to the computer where Cognos Analytics is installed. For example, copy the file sqlcert.cer to the root directory, c:\sqlcert.cer
- Type
cd C:\Program Files\ibm\cognos\analytics\ibm-jre\jre\lib\security - Type , for example, C:\Progra~1\ibm\cognos\analytics\ibm-jre\jre\bin\keytool -import -trustcacerts -file "c:\sqlcert.cer" -keystore cacerts -alias SQLCert
- Start Cognos Configuration using cogconfig.bat you modified in the previous step. Important: You must start IBM Cognos Configuration using cogconfig.bat
To get the SQL Server Native Client Connection:
Download a version of the Native Client that supports TLS 1.2 as per Microsoft's KB Article: https://support.microsoft.com/en-ca/help/3135244/tls-1-2-support-for-microsoft-sql-server
To get the JDBC data source connection working:
- Go to Cognos Administration -> Configuration tab -> Dispatchers and Services
- Click on the dispatcher server to drill down to the services
- Beside the QueryService, click the Set Properties button
- Go to the Settings tab
- Add the following the Additional JVM Arguments for the QueryService setting
-Dcom.ibm.jsse2.overrideDefaultTLS=true
- Click OK
- Click on the Status tab
- Select System
- Click on the server to drill down to the services
- Beside QueryService, click the drop down arrow
- Select "Stop immediately"
- Wait 30 seconds for it to fully stop
- Click the drop down again and select "Start immediately"
Related Information
Was this topic helpful?
Document Information
Modified date:
05 May 2020
UID
swg22016796