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. 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, they files will need to be extracted under the Cognos installation location to be installed. 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'll be done in the “IBM Cognos Configuration” utility. There'll be 2 areas to modify. The first will be Security > Cryptography. In here you'll want click edit on “SSL Protocols” and set this to only TLS 1.2, as seen in this screenshot:
    TLS12.png


    After this, you'll 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'll 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”. This should now be successful

After these steps the test connection should be successful and the Cognos services can be started, the FNMS Analytics should now be accessible. If you are not seeing any of the data in the reports or widgets loading, you may need to take some additional steps to set a JVM argument for the QueryService to use TLS. The below IBM KB details these settings. If you do not see these settings in the Admin Console, please open a support case for Flexera support to assist with getting access to these settings.  

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.

We had to complete a few other steps within the Cognos configuration to get TLS 1.2 connection working if you use the JDBC connection: (Discovered this working with Flexera engineers) 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"

I am currently implementing FNMS2020R2. This has a newer Cognos version 11.0.13. and running in to the issue that reports are not being loaded because of db connection. (testing connection works fine) This issue is being caused by TLS 1.2 and SQL server 2017.  I have performed all steps and now need to :

click the Set Properties button: OK Go to the Settings tab Add the following the Additional JVM Arguments for the QueryService setting.

The settingst tab is not availlable in this cognos version.

Regards

Ronald

@Ronny_OO7  Even I have the same issue. Couldn't find settings tab.

QueryService-Screenshot.jpg

Here is what I see within the properties view of QueryService.

I have never been able to edit any of the services under "Dispatcher and Services" in any of the IBM Cognos Analytics previous and current versions.

This is exacltly what I also see. Couldn't find settings tab.

@Ronny_OO7 

Are you able to test the JDBC connection from the Data Source (FNMP-Dashboard) within the IBM Cognos Administration view?

If so, are you getting any error messages stating that the JDBC connection failed?

If you do not see the settings, you need to open a support case to get the steps to enable the settings, as this is not something that can be published in the article. This is mentioned towards the bottom of the article:

 If you do not see these settings in the Admin Console, please open a support case for Flexera support to assist with getting access to these settings.  

@davidle , @Ronny_OO7 
The reason for not seeing the settings under the properties for query service is because the admin privilege is restricted.
I would recommend you work with Flexera support to grant the service account full admin privilege. This will then display the additional properties page, and you should be able to add the needed changes for TLS 1.2.

@davidle and @WheresThePizza 

Thanks for your help.  Yes the test connection works fine.

I have created an case and received the sql command to update the rights. Unfortunate the result is the same. Also I wonder if I can share the command here? As it is hacking FNMS a bit..

@Ronny_OO7 Did you restart the Cognos services after running the SQL command? The command should be run against the content store database. Cognos services should be restarted. 

Thanks 

@Darshana  No I didn't but did now and it works. Only I now have an new issue: We can’t retrieve the data from data set Flexera Data Models. 

I have checked the connection details and Native client works but jdbc is failing. Now I wonder how should the connection look like?

;LOCAL;OL;DBInfo_Type=MS;Provider=SQLNCLI11;Data Source=fnmsdbserver.ont.customer.nl;Integrated Security=SSPI;Provider_String=Initial Catalog=FNMS_FM;@COLSEQ=IBM_JD_CNX_STR:;LOCAL;JD-SS;URL=jdbc:sqlserver://fnmsdbserver.ont.customer.nl:1433;DATABASE=FNMS_FM;LOGINTIMEOUT=600;integratedSecurity=true;DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

 

 

@Ronny_OO7  Looking at the connection string, it looks like you are connecting to FNMS_FM database. 
There are ideally 3 data source connections we provide. FNMP-Dashboard, FNMP-DW-DS, and FNMP-DW-DS-DQM.
FNMP Dashboard connects to the FNMS Compliance database, while the latter two connect to the FNMS DataWarehouse.
Are you getting the same error while testing the connection to all three DSNs?
Flexera Data Models makes use of the datawarehouse. Can you confirm that the DW-DS and DW-DS-DQM connections point correctly to the FNMS Datawarehouse database?

Also, since it looks likethe Native connection is working, to get the JDBC connection started, you would need to do the following: (as mentioned in the above comments)

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 "   (Note: you need to include the hyphen)

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"

@Darshana thanks for your help. The connection string had the wrong connection db. After changing this and testing at all 3 data source connections it now works and loads correctly Thank you very mutch for your help.  Next step now is to make the connection fully HTTPS, will follow the steps for that. Regards Ronald

@WheresThePizza @Darshana  i have the same challenge with once my customer , i cant see the settings tab under Query services .

 

Can you please let me know the referral case number that was raised for full admin rights , so that i can request support to refer the same.

 

Regards,

Junaid Vengadan

@junaid_vengadan 
There would be no need to mention the referral case number. You could simply submit a supprot case with Flexera and ask them to grant full admin right for the service account for the TLS 1.2 SQL Server (Cognos Analytics) issue.

Version history
Revision #:
9 of 9
Last update:
‎Dec 26, 2019 11:08 AM
Updated by: