How to solve Microsoft SQL Server. Error 233. Prov

Question

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 the following Error:

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

I tried logging in with "Windows Authentication" and the error was same. I also tried solutions for similar SQL Error 233 issues but with no result. All the SQL Error 233 issues I came across on Stack Overflow are related to Shared Memory provider but not SSL Provider.

Finally I came across this post: https://www.sqlservercentral.com/Forums/Topic1810025-3411-1.aspx and tried the following solutions:

• Enabled Ciphers RC4 128/128, RC4 50/128, RC4 56/128
• Enabled TLS 1.0 Server Protocol
• Disabled SSL 2.0 (Registry does not have SSL 3.0) [also tried
  enabling SSL 2.0 but didn't work as well]
• Enabled TLS 1.0,1.1,1.2 Protocols althogether as well.

But none of them worked and we still couldn't log in to SQL Server Management Studio. Any help or suggestion is highly appreciated. Thank you in advance.

Answer 1

After much tinkering I found out that the Windows update have modified the TLS and SSL settings wrong. I had to specifically make changes to the Cipher Suits and Protocols in the registry in order to make it work and now we can log in to SSMS successfully (both locally and remote log in). So the specific changes I have made to registry is 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) I 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.

Hope this helps!