SQL Server Troubleshooting: Error 233 - SSL Provider

  • Article

Introduction

After the global RansomeWare attack, we updated our Windows Server 2012 R2 only to find out that our SQL Server Management Studio (SSMS) stopped working. Whenever we try to connect to SSMS locally or remotely it provides us with the following Error:

A connection was successfully established with the server, but then an error occurred during the login process. (Provider: SSL Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Even after trying to log in with "Windows Authentication" and the error was same. Also tried solutions for similar SQL Error 233 issues but with no result.

The Solution

After much tinkering, the finding was that the Windows Update has modified the TLS and SSL settings in a wrong way. Specific changes had to be made to the Cipher Suits and Protocols in the registry in order to make it (SSMS) work again and now we can log in to SSMS successfully (both locally and remote login). So the specific changes which had to be made to the registry are as follows:

Changes in Schannel:: (Inlcuding Client Side Protocols)

Protocols Enabled: Multi-Protocol Unified Hello, PCT 1.0, TLS 1.0

Protocols Disabled: SSL 2.0, SSL 3.0, TLS 1.1, TLS 1.2

Ciphers Enabled: NULL, DES 56/56, RC2 40/128, RC2 56/128, RC2 128/128, RC4 40/128, RC4 56/128, RC4 64/128, RC4 128/128, Triple DES 168, AES 128/128, AES 256/256

Hashes Enabled: MD5, SHA, SHA 256, SHA 384, SHA 512

Key Exchanges Enabled: Diffie-Hellman, PKCS, ECDH

Cipher Suite Changes:: 

Disabled: SSL_CK_RC4_128_WITH_MD5, SSL_CK_DES_192_EDE3_CBC_WITH_MD5

Enabled: Everything else realted to TLS

To achieve this easily (changes to security and modification of registry) Also used this tool (IISCrypto): https://www.nartac.com/Products/IISCrypto

Do make sure that each cipher or protocols mentioned here are specifically enabled or disabled, or else the issue won't be resolved.

References

  1. Initial Solution is provided in Stack Overflow [Link: https://stackoverflow.com/questions/44067698/how-to-solve-microsoft-sql-server-error-233-provider-ssl-provider]
  2. Similar but different solutions for the same issue can be found at www.sqlservercentral.com [Link: https://www.sqlservercentral.com/Forums/Topic1810025-3411-1.aspx]