Sql-server – Error after applying SQL Server 2008 R2 Upgrade 10.50.6542 (Latest Build)

patchingsql servertls-1.2

First the problem: Since I applied the latest patch to SQL Server 2008 R2 (released on March 3, 2016) that should have taken my server to build 10.50.6542 I can't connect anymore. This patch is to provide support TLS 1.2.

The error I'm getting (when attempting to connect using a client tool directly in the server) is 

"A connection was succesfully established with the server, but then an error occurred during the pre-login handshake"

enter image description here

The server is a Windows Server 2012 R2 and is running NET Framework 4.6.1 (some posts point to older version of .NET framework causing this problem, but this is not my case).

I tried to run the following packages to see if this would fix…

  • SQL Server Native Client (x86 and x64)
  • Hotfix rollup 3106993 for the .NET Framework 2.0 SP2 in Windows Server 2012 R2 and Windows 8.1
  • Hotfix rollup 3106994 for the .NET Framework 4.0 in Windows
  • Hotfix rollup 3099845 for the .NET Framework 4.5.2, 4.5.1, and 4.5

… None of these were necessary, and nothing helped…

Has anybody experienced this? Any ideas or suggestions?

Thanks!

One Update 24 hours later
I inspected the SQL Server error log looking for entries that include only a Start, 5 failed login attempts and a stop. From all the messages in that time-frame (107 entries in total) the only message that includes anything like an error is 

 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

This message though… has been reported on server startup even before I applied the patch so I am 100% confident that it is a red herring. No logs are inserted in the SQL Server error log as a result of the failed login attempts.

Another Update

Based on suggestions I got from people here, I downloaded and installed the latest version of SSMS (17.3) on the server. When I tried connecting this time, I got a different error so it seems I'm moving further, but I'm not there yet.

enter image description here

So it seems that it is true that old versions of SSMS do target .net 3.5 and even if you have newer versions of the .net framework the client connections will not work, the question is now why is that I still get this "No process is on the other end of the pipe", even when most of the client components have been updated.

Some more things I have investigated (and discarded)

  • The “client protocol” order in SQL Server Configuration Manager seems to be the correct one, as TCP/IP is at the top. This was suggested as a possible cause of trouble, but in my case it is all appropriately configured (apparently)

enter image description here

  • Tried to install “Microsoft ODBC Driver for SQL Server” as suggested in the TLS 1.2 support for Microsoft SQL Server but that didn’t make a difference either. After installing, the error message is the same

  • Tried adding the registry keys at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2 (also as suggested in the same article). Same error…

Best Answer

Finally! after 3 days of trying everything under the sun I found the root cause of my problem.

The aha! moment was when I got together with a group trying to understand why this patch was deploying just fine in other servers while not in this particular one.

We noticed that something different (among other things... that we listed in a neatly organized notepad) was that this SQL Server 2008 R2 was configured for encryption in transit (a.k.a. SSL encryption) via certificates and some settings on SQL Server Configuration manager namely:

  1. On the Protocols for [Instance_Name] and then Properties (certificates tab) we had select the certificate we have created for the purpose of encrypted communications.

  2. On that same window (flags tab) we set ForceEncryption option for the Database Engine to Yes

So we started to think... what if the problem is the certificate itself?. Then we went ahead and remove the encryption configuration (the patch for SQL Server Build 10.50.6542 still applied) and the connection started working, confirming that the combination of the patching plus our SSL configuration was bonkers.

After more investigation and via a process of elimination, we narrowed down the problem to the certificates we were using. The certificate was created back then with makecert (to create a self signed certificate) and was NOT using the -a parameter. This parameter tells makecert what Hash algorithm to use. If not provided, makecert will use the default MD5 signature hash algorithm which is not compatible with TLS 1.2.

Also on the TLS 1.2 RFC you can see that explicitly…

MD5
  MD5 [MD5] is a hashing function that converts an arbitrarily long
  data stream into a hash of fixed size (16 bytes).  Due to
  significant progress in cryptanalysis, at the time of publication
  of this document, MD5 no longer can be considered a 'secure'
  hashing function.

So, in order to fix this problem, the solution is to add the -a SHA256 parameter to makecert (make sure you are using makecert version 6.3.9600.17298 and not the old one 5.1313790.0 because that one does not support SHA256 as a parameter).

I'm writing all these since the whole conundrum took me 3 days to figure out and I hope some day someone is going to be benefited from this. If only Microsoft would have included in BIG BOLD letters that MD5 is no longer supported right in the article where the patch is described!

Thanks to @Mr.Brownstone and @sepupic for all their help!

Related Question