SDM: install fails when connecting to SQL Server database with error "Existing connection was forcibly closed by the remote host". Client unable to establish connection."

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• KNOWLEDGE TOOLS
• CA Service Management - Service Desk Manager

Issue/Introduction

CA SDM install fails at the 'Integrate the Products' step of the Installation Wizard.  Previous steps of the installation were successful.

CA SDM stdlogs show below errors:

INFO  PatchDatabaseTask.java   353 Executing altArgs >sql_check_db -c  -s SDMServerName -o mdbadmin -d mdb -U mdbadmin -p <DB password> -n SQLServerHostName <

INFO  ?                        ? Process D:\PROGRA~1\CA\SERVIC~1\bin\sql_check_db.exe completed with exit code 99

ERROR PatchDatabaseTask.java   607 Can't connect to the database.

ERROR PatchDatabaseTask.java   665 Can not connect to the Database Server.

CA SDM Checkdb.log shows:

SDMServerName  sql_ckeckdb          2312 ERROR        sqlclass.c             473 Failed to logon to SQL Server (SQLServerHostName ) Reason: [Microsoft SQL Server Native Client 11.0] [ SQL Code=10054 SQL State=08001] Client unable to establish connection; [Microsoft SQL Server Native Client 11.0] [ SQL Code=10054 SQL State=08001] TCP Provider: An existing connection was forcibly closed by the remote host.  

SQL command line tool test (osql) errors out with the same behavior, while sqlcmd command works fine. See below:

osql -E -S SqlServerHostName

[SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host. 

[SQL Server Native Client 11.0]Client unable to establish connection 

 

sqlcmd -E -S SqlServerHostName

1>

 The above indicates that the behavior is more about SQL Native Client rejecting the connection but regular SQL client layer works fine.

End user may also experience problems with running pdm_configure or just starting SDM Services.

Cause

SQL Native Client layer cannot connect to SQL Server after the TLS 1.2 protocol is enabled on the SQL Server.

Such issues happen when the SQL Native Client layer does not support the TLS 1.2 restrictions when compared to the SQL Server layer.  

This is documented at length at https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

Environment

SQL Server 2014 or 2016 in a TLS 1.2 enforced environment

CA Service Desk Manager 14.1 or 17.0

Resolution

As documented in Microsoft article https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server, to resolve this issue, download and install the SQL Native client fix that is listed in the Client Components downloads section of this article (https://www.microsoft.com/en-us/download/details.aspx?id=50402)

Additional Information

What is the correct registry setting to enable TLS 1.2 for SQL Server communication?

The correct registry settings are as follows: 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 

 

The information in this article has been included in our product documentation. You can find further details here:

https://docops.ca.com/ca-service-management/17-0/en/troubleshooting/troubleshooting-ca-service-desk-manager/ca-service-desk-manager-17-0-installation-fails