Product:
Cognos Enterprise Planning 10.2.1
Microsoft Windows 2012 R2 Server
Problem:
When doing a large publish from inside Contributor Administration Console (CAC) to a SQL 2017 server an a different computer, you get a error at random times. It can work, but most of the time the publish fail.
The cognos planning publish process stop at different BCP jobs, and you can find the errors in the -out file. Best practice is to set the cognos planning services system path to a separate folder, then can you easy find the log files for cognos planning.
Error message:
Error Number: -2147220469
Line Number: 400
Description: Unable to execute Job Item
Execution failed after 1 attempt(s).
Unable to Bulk Load the data.
Error found in output.
SQLState = 08001, NativeError = 10054
Error = [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: An existing connection was forcibly closed by the remote host.
SQLState = 08001, NativeError = 10054
Error = [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
Suggested Solution:
On both your SQL server and Cognos Planning server update the registry with this values, to turn off TLS 1.2 usage:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000000
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000000
TCP/IP Registry Values That Harden the TCP/IP Stack ( Windows 2003 Servers )
If that does not help then add this value on the SQL server in registry;
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
“SycAttackProtect”=dword:00000000
This turn of DoS protection.
https://social.technet.microsoft.com/Forums/en-US/c55aa101-059d-491e-9fc2-73b5d8171eb8/protect-against-syn-attacks?forum=winservergen
You may need to reboot the servers to make the change take affect.
And if that does not help – install a later SQL driver on you cognos planning server, download from Microsoft and install.
https://www.microsoft.com/en-us/download/details.aspx?id=56567
You can check the version of SQL driver you have, by going to the DOS prompt, and enter commando:
bcp /v
to see version in use.
You may need to update the PATH on your windows server, to have the new driver folder first in path – so it is used by Cognos Planning.
Go to control panel – system – advance system settings
Click on Advance tab – click on Environment Variables
In System variables select path and click Edit
Add the SQL driver path to beginning of the path like; %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\
More information:
https://support.microsoft.com/sv-se/help/3135244/tls-1-2-support-for-microsoft-sql-server
https://community.hostek.com/t/ssl-security-error-for-microsoft-sql-driver/348