Wednesday 10 January 2018

SQL Server Connectivity issue



I recently received an escalation of an issue where an application server was not able to connect to few of the database servers however one production database server was accessible. While connecting from SQL Management Studio following exception was raised.


A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)


This exception shows that the connectivity between application and database instance is fine, however, during login handshake there is some issue.


I enabled below server traces to identify more details of socket errors.


DBCC TRACEON (3689, 4029, -1)


After enabling these traces, I notice below error that gave more information to understand the issue.


Network error code 0x80090331 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 2 ms, enqueued 0 ms, network writes 0 ms, network reads 2 ms, establishing SSL 2 ms, negotiating SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms.


This error indicate that both servers are using different ciphers to encrypt while making connection.
I found that Triple DES 168 was mismatching with Application server so just removed and it resolved the problem.




Reference:


https://blogs.msdn.microsoft.com/winsdk/2014/05/01/encountering-the-client-and-server-cannot-communicate-because-they-do-not-possess-a-common-algorithm-or-sec_e_algorithm_mismatch-0x80090331/


https://social.technet.microsoft.com/Forums/office/en-US/bcc6be0b-0385-4aff-82ae-b6e973fe398d/0x80090331-the-client-and-server-cannot-communicate-because-they-do-not-possess-a-common-algorithm?forum=winserver8gen