This is a 3rd installment of the error “The client and server cannot communicate, because they do not possess a common algorithm“. You can find the first 2 blogs here:

Part 1
Part 2

In Part 1, I alluded to another cause of the SCHANNEL error, and it boils down to issues with the Ciphers being used in the TLS exchange. There were 2 updates to Windows (KB3161639 and KB3161608) that introduced 2 new ciphers, and changed the default priority of ciphers being used.

1. TLS_DHE_RSA_WITH_AES_128_CBC_SHA
2. TLS_DHE_RSA_WITH_AES_256_CBC_SHA


If you’re seeing the below error when trying to connect to SQL Server with TLS 1.2, and you are sure that all SQL Server components have been updated, we’re likely running into a Cipher issue. It should be a consistent error, and you would get the error from all applications trying to connect to SQL Server (given the same provider being used). You’ll want to check if force encryption is disabled or not being used, and no SSL Certificate has been imported into SQL.

Error(s): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. Provider: SSL provider. The client and server cannot communicate because they do not possess the common algorithm.


If you are using an SSL Certificate with your SQL Server, the first step is to ensure that the Certificate Hash in the registry matches the Certificate Thumbprint of the SQL Server SSL Certificate being used:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\SuperSocketNetLib



Resolution

There’s 2 resolutions you can deploy.

1. Disable Cipher Suites Order.
a. Start –> Run –> gpedit.msc
b. Expand “Computer Configuration” –> Administrative Templates –> Network –> SSL Configuration Settings
c. On the right panel you’ll see “SSL Cipher Suite Order”. Double click and change the state to “disabled” –> Click Ok
d. Restart the server


Both of the options above require a reboot, so you will want to inform your users of the downtime.

Another note I would like to make about enabling/disabling TLS 1.2 is that for Windows Server 2012 and above, TLS 1.2 is enabled by default. There is no need to specifically add the TLS 1.2 key in the SCHANNEL Protocol regkey to enable it. With Windows Server 2008 R2 and below, you do have to specify this key if you want to enable TLS 1.2.

The reason I mention this is because if you accidently disable TLS 1.0 and SSL 3.0 on your Windows Server 2008 R2, and don’t specify the key to enable TLS 1.2, you’ll need to deploy other means of accessing your server remotely, since all protocols have been disabled.

Most likely, you’ll need to contact your Domain Admins to issue a GPO for your server and enable one of the SCHANNEL Protocols for you to log into the server.

Be careful when enabling/disabling these protocols, and make sure that you know what you are doing. I’ll give the same disclaimer here:


DISCLAIMER: Change your registry settings at your own risk. Serious problems might occur if you modify the registry incorrectly. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. I don’t recommend anyone changing their registry keys without fully knowing what they will do. If you do make changes to the registry, those settings will only take effect after a reboot.


Reporting Services 2016

If you find that you are still having errors with certain aspects with Reporting Services 2016 after disabling TLS 1.0, you also might look into deploying the following fix: https://support.microsoft.com/en-us/help/3206898/enabling-iis-manager-and-web-deploy-after-disabling-ssl3-and-tls-1.0

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001


I hope this post combined with the previous posts will fix your SCHANNEL errors connecting to SQL Server, but if they don’t, you can try a series of Logman traces on both sides of the communication that might give you more clues on the SCHANNEL error:

Schannel tracing on Server and Client while reproducing the issue

To Start tracing:

logman create trace “ds_security” -ow -o c:\ds_security.etl -p {37D2C3CD-C5D4-4587-8531-4696C44244C8} 0xffffffffffffffff 0xff -nb 16 16 -bs 1024 -mode Circular -f bincirc -max 4096 -ets

logman update trace “ds_security” -p “{1F678132-5938-4686-9FDC-C8FF68F15C85}” 0xffffffffffffffff 0xff -ets

To Stop tracing:

logman stop “ds_security” -ets