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:
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.
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.
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
Resolution
There’s 2 resolutions you can deploy.
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:
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
No Comments Yet