cancel
Showing results for 
Search instead for 
Did you mean: 

Analytics/Cognos – Connection to SQL Server Fails When Server is Configured to use TLS 1.2

Analytics/Cognos – Connection to SQL Server Fails When Server is Configured to use TLS 1.2

Symptoms: When trying to connect Cognos to your SQL Server, you may see an error like:

Error: "SQL Server did not return a response. The connection has been closed." 

Diagnosis: If your SQL Server is configured to only communicate via TLS 1.2, you will see connection errors until a few additional steps are taken to configure Cognos to support only TLS 1.2. 

Solution: The following steps can be taken to configure Cognos to communicate only via TLS 1.2:

  1. You will first need to get and install the Unrestricted SDK JCE policy files. These can be obtained Here
    Note: You will be required to create an IBM login to download these files

  2. Once downloaded, to install the files, they will need to be extracted under the Cognos installation location. By default, this location will be: C:\Program Files\ibm\cognos\analytics\jre\lib\security

  3. Next, you will need to add the SHA256 ciphersuites. This will be done in the “IBM Cognos Configuration” utility. There will be 2 areas to modify. The first will be Security > Cryptography. In here you will want click edit on “SSL Protocols”, and set this to only TLS 1.2, as seen in this screenshot:
    TLS12.png


    After this you will need to go into Security > Cryptography > Cognos, click edit on “Supported ciphersuites”, and add all of the ciphersuites that have “SHA256”, as seen in this screenshot:
    Ciphersuite.png
  4. Once these steps are completed, you will want to close the “IBM Cognos Configuration” utility.

  5. Open the bin64 folder under the Cognos installation directory, by default this will be: C:\Program Files\ibm\cognos\analytics\bin64

  6. Locate startwlp.bat, open this in a text editor, and find the following line:
    set JVM_ARGS=-Xmx4096m -XX:MaxNewSize=2048m -XX:NewSize=1024m %DEBUG_OPTS%

  7. After this line add the following:

    set JVM_ARGS="-Dcom.ibm.jsse2.overrideDefaultTLS=true" %JVM_ARGS%

  8. Save and close this file

  9. Locate bootstrap_wlp_os_version.xml, open this in a text editor, and find the following line: <param condName="${java_vendor}" condValue="IBM">-Xscmaxaot4m</param>

  10. After this line add the following:

    <param>"-Dcom.ibm.jsse2.overrideDefaultTLS=true"</param>

  11. Save and close this file

  12. Locate cogconfig.bat, open this in a text editor, and find the following line:
    set J_OPTS=%DD_OPTS% %J_OPTS%

  13. After this line add the following:

    set J_OPTS="-Dcom.ibm.jsse2.overrideDefaultTLS=true" %J_OPTS%

  14. Save and close this file

  15. Start "IBM Cognos Configuration" using cogconfig.bat you modified in the previous step. Important: You must start "IBM Cognos Configuration" using cogconfig.bat

  16. In “IBM Cognos Configuration”, go to Data Access > Content Manager > Content Store.

  17. Right click on Content Store, and choose “Test”

After these steps the test connection should be successful, and the Cognos services can be started, and the FNMS Analytics should now be accessible. For more information on this issue, you can refer to the following IBM KB: Connection to SQL Server fails when the server is configured to use TLS 1.2 or connecting to SQL Ser...


Tags (2)
Was this article helpful? Yes No
100% helpful (1/1)
Comments
If you have problems with downloading the "Unrestricted SDK JCE policy files" from IBM, try this link: https://www-01.ibm.com/marketing/iwm/iwm/web/dispatcher.do?source=jcesdk. The link from original post didn't work for me after login, but this one worked.

This article helped me to solve my problems :) Thanks.

It would be nice if the IBM Cognos Analytics in FNMS Analytics would be updated to support TLS 1.2 out of the box.

I'll add some error texts here because it took me about 10 hours to went through various options while I found this article which actually was the one which solved it. And it was only because my error text was not in this article. So now it will be :)

My environment: Windows servers 2016, MSSQL server 2017, FNMS 2019R2

Error message of .\installCognos.ps1:

 

Invoke-CognosConfigTool : Exception calling "Invoke" with "2" argument(s): "Cognos configuration failed (error code 2). Check the cogconfig.*.log and cogconfig_response.csv files found in C:\Program Files\ibm\cognos\analytics\logs for
more information."
At D:\temp\FNMS\Support\modules\ConfigureCognos.psm1:160 char:2
+     Invoke-CognosConfigTool $CognosInstallDir -ErrorHandler ${functio ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-CognosConfigTool], MethodInvocationException
    + FullyQualifiedErrorId : RuntimeException,Invoke-CognosConfigTool

 

When looking into cogconfig_response.csv I can see this error at the end of file:

 

EXEC, "[Content Manager database connection]", "Testing Content Manager database connection."
ERROR, "[Content Manager database connection]", "The database connection failed."
ERROR, "[Content Manager database connection]", "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."

 

At this point the installation broke and PowerShell script InstallCognos.ps1 ended, so I started  "IBM Cognos Configuration" utility by running C:\Program Files\ibm\cognos\analytics\bin64\cogconfig.bat and then I followed the steps written above in the article...

ContentStore database connection test was successful, so I started again the InstallCognos.ps1 from the PowerShell and now it finished installation without any problems.

Version history
Revision #:
7 of 8
Last update:
‎Aug 28, 2019 10:20 AM
Updated by:
 
Contributors