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.
The following steps can be taken to configure Cognos to communicate only via TLS 1.2.
NOTE: You will be required to create an IBM login to download these files.
IMPORTANT: You must start IBM Cognos Configuration using cogconfig.bat.
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.
on Aug 28, 2019 10:00 AM - edited on Jul 31, 2024 01:47 PM by HollyM
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.
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.
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.
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.
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
@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.
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?