TCP Provider: An existing connection was forcibly closed by the remote host

by ·Comments Off on TCP Provider: An existing connection was forcibly closed by the remote host

Product:

Cognos Controller 10.4.2
LICENSE_CONTROLLER_version=LICENSE_CONTROLLER-AW64-ML-RTM-10.4.2000.2-0
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2000.1098-0

Microsoft Windows 2016 Server

Microsoft  SQL server 2019

Issue:

After change in Windows Server for SQL database, to not allow TLS 1.0 and TLS 1.1 protocol the Cognos Controller and FAP program can not connect to the SQL database. You have not enabled HTTPS in Cognos Configuration, therefor Cognos is not using SSL to the clients.

Cognos Analytics who uses the SQLJDBC42.JAR driver, works fine.

Error message:

TM1.SQLAPI   08001[Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: An existing connection was forcibly closed by the remote host.

TM1.SQLAPI   E16) Cannot connect to ODBC data source “ControllerDatabase”  08001[Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: An existing connection was forcibly closed by the remote host.

Above error when you test the UDL connection to the SQL server.

Above error when you try to login to Cognos Controller.

Microsoft SQL server Native Client 11.0 Client unable to establish connection
TCP provider: An existing connection was forcibly closed by the remote host.
in Windows event log:

Could not connect to database. Database CTRL blacklisted. It will be considered again in 20 minutes.
Client unable to establish connection
TCP Provider: An existing connection was forcibly closed by the remote host.

Solution:

Upgrade the SQLNCLI.MSI program to a later version.

Download a new SQL 2012 driver from Microsoft and install that in your Controller Windows server.
https://www.microsoft.com/en-us/download/details.aspx?id=50402

Also install same SQLNCLI.MSI driver on your TM1 server where you have the FAP service running.

Above is the working driver, you should download from Microsoft. Driver version 11.4.7001.0 is correct.

Go to the Control Panel on your Cognos Controller computer and check installed version of SQL 2012.

Above is the working version of SQL 2012 driver, if you not have it – you must install it for Cognos Controller.

Above drivers does not work when you have turned off TLS 1.0 and TLS 1.1, to the SQL server.

When you install the new SQL client, you need to stop IBM Cognos Controller Batch Service and IBM Cognos FAP Service. Otherwise you will be reminded as below.

More Information:

https://www.ibm.com/support/pages/sqlncli111-provider-not-registered-local-machine-when-testing-database-connection

https://docs.microsoft.com/en-us/answers/questions/28268/sql-native-client-110-support.html

https://www.ibm.com/support/pages/ssl-provider-error-0-existing-connection-was-forcibly-closed-remote-host-error-when-launching-controller-client

https://docs.microsoft.com/en-us/troubleshoot/windows-server/identity/apps-forcibly-closed-tls-connection-errors

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

https://www.ibm.com/support/pages/how-enable-ssl-https-cognos-controller

https://www.ibm.com/support/pages/how-configure-controller-use-tls-formerly-ssl-connect-microsoft-sql-database-transit-database-encryption