Analytics/Cognos connection to SQL server fails when server is configured to use TLS 1.2

[#1]

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

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

If your SQL Server is configured to communicate only via TLS 1.2, you will see connection errors until you take a few additional steps 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. Download and install the Unrestricted SDK JCE policy files, which can be obtained from IBM’s website.
   

   NOTE: You will be required to create an IBM login to download these files.

2. Once downloaded, the files must be extracted to the Cognos installation location. By default, this location is: C:\Program Files\ibm\cognos\analytics\jre\lib\security
   
   
3. Add the SHA256 cipher suites in the IBM Cognos Configuration utility. In the IBM Cognos Configuration utility, go to Security > Cryptography. Then select SSL Protocols to edit and set this to TLS 1.2, as seen in the image below.
   
   
   
   Then go to Security > Cryptography > Cognos, select Supported ciphersuites to edit, and add all the ciphersuites with SHA256.
   
4. Close the IBM Cognos Configuration utility.
   
   
5. Open the bin64 folder under the Cognos installation directory. By default the location is: C:\Program Files\ibm\cognos\analytics\bin64
   
   
6. Locate startwlp.bat. and open this in a text editor. Then, 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 the 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 the 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 the file.
    
    
15. Start the IBM Cognos Configuration utility using the cogconfig.bat you modified in the previous step. 

IMPORTANT: You must start IBM Cognos Configuration using cogconfig.bat.

16. In the IBM Cognos Configuration utility, 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. FlexNet Manager Suite Analytics should now be accessible.

If you do not see any of the data in the reports or widgets loading, you may need to take additional steps to set a JVM argument for the QueryService to use TLS. The IBM article below 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, refer to the following IBM article: Connection to SQL Server fails when the server is configured to use TLS 1.2 or connecting to SQL Server 2016.

[pavol_holes]

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.

[pavol_holes]

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.

[jrodriguez321]

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"

[Ronny_OO7]

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

[msutharh]

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

[davidle]

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.

[Ronny_OO7]

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

[davidle]

@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?

[WheresThePizza]

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.  

[Darshana]

@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.

[Ronny_OO7]

@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..

[Darshana]

@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. 

[Ronny_OO7]

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

[Darshana]

@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?

[Darshana]

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"

[Ronny_OO7]

@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

[emtmeta]

@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

[Darshana]

@emtmeta 
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.

[anhpham1652]

Hello,

I followed this link to configure TLS 1.2 SQL Server and IBM Cognos. All database connection is OK (both on IBM Configuration and Cognos Administration) but there is no data available at section Software Asset Management, etc. 

I've found some error such as "query service internal error has occurred please see the log for details" and in xqe log I've got this xqe log.

https://drive.google.com/file/d/1u6s18xzvWg1isMJhx9ejFRD7GzIFmh1q/view?usp=sharing 

What should I do to fix it?