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…

Improve this question
11
  • hope u looked through this - stackoverflow.com/questions/27267658/…
    – DMDM
    Oct 25, 2017 at 22:20
  • Check SQL Server error log. there will be some clue, maybe it does not see certificate installed and so on
    – sepupic
    Oct 26, 2017 at 8:36
  • thanks for your suggestion @jesijesi. I did read that entry in stack overflow (from top to bottom) but all the suggestions do not apply to my case. I tried Windows Authentication as well and the problem is the same. Also, my problem is not remote connectivity (as is the case with may users on that blog post) I get this error even when I'm using a client tool right at the server.
    – Martin Surasky
    Oct 26, 2017 at 17:49
  • I would love to check the error log @sepupic but unfortunately as I mentioned I have no way of starting a T-SQL server session against the server, hence I cant query the error log via xp_readerrorlog. Also, viewing the error log via SSMS is not a possiblity either (again, I can't connect to the server).
    – Martin Surasky
    Oct 26, 2017 at 18:05
  • You can open error log in any editor, for example in notepad
    – sepupic
    Oct 26, 2017 at 18:08

2 Answers 2

Reset to default
2

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!

Improve this answer
1

TLS 1.2 wasn't made the default security protocol until .NET 4.6 and wasn't even a supported option until .NET 4.5 so if you are using a client that targets a .NET runtime of less than 4.5 it won't be able to communicate using TLS 1.2 by default.

In your specific scenario, the client happens to be SQL Server Management Studio. Versions of management studio up to and including 2014 used .NET 3.5 SP1 as the target runtime - which unfortunately will prevent you from connecting using TLS 1.2 as is.

To remedy your problem, upgrade your installation of management studio to the latest version as this targets .NET 4.6.1 and will use TLS 1.2 as the default security protocol.

SSMS Download Page

A second solution, if you are unable to update to the latest version of SSMS, is that you can make some registry changes that will force Windows Server to ignore the protocol specified by .NET 3.5 and to use TLS 1.2 instead. I would recommend against this (and if you can't update SSMS you are probably not likely to be allowed to change registry settings) but I have included the information for completeness.

Details on how to force .NET 3.5 to use TLS 1.2 and which registry keys need to be changed can be found below:

Support for TLS System Default Versions included in the .NET Framework 3.5 on Windows Server 2012

Improve this answer
7
  • Thanks for all your help @Mr.Brownstone. I tried following your recommendation and installed the latest version of SSMS 17.3. The new version seems to get further in reaching the server but there is still an error message (although this time it comes from the SSL Provider, which makes me think I'm moving in the right direction). I will update my original post with the findings and print-screens to see if you (or someone else) can provide ideas or some suggestions.
    – Martin Surasky
    Oct 26, 2017 at 22:52
  • @MartinSurasky Check your client protocols in Configuration Manager, ensure that TCP/IP is the top of the list.
    – Mr.Brownstone
    Oct 27, 2017 at 0:49
  • Yes @Mr.Brownstone, TCP/IP is the first one (see the print-screen on the edited original post with how it looks on my server)... also, I was under the impression that TCP/IP is indeed used as the error message now starts with "provider: SSL Provider" (and as far as I know SSL runs on top of TCP/IP). Notice how originally the error I got (first print screen on this post) started with "provider: Shared Memory Provider
    – Martin Surasky
    Oct 27, 2017 at 13:33
  • There are a few registry settings on Microsoft Help (near the bottom of the page) can you check the values that you have configured against those?
    – Mr.Brownstone
    Oct 27, 2017 at 13:53
  • Yes @Mr.Brownstone, I tried adding the registry keys at (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2). Same error…I'm not surprised that didn't help as the article says that those entries are not needed only in Windows Server 2012 and later (and my box is a Windows Server 2012 R2). Having said that, I tried it anyways (at this stage in the game I'm not discarding ANYTHING... if it could help) but it did not make a difference.
    – Martin Surasky
    Oct 27, 2017 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.