18 thoughts on “Intermittent TLS issues with Windows Server 2012 R2 connecting to SQL Server 2016 running on Windows Server 2016 or 2019”

1. That is some mighty fine sleuthing and wonderful blogging! I’ve encountered the same issue and I was wondering if it would be possible to share the test code since as you’ve noted, this issue occurs very intermittently and so testing resolution is quite painful without a reliable way to test? For us the issue seems to have begun after to occur once a Windows Server 2016 CU post Nov 2019 was installed (i.e. any CU from Dec 2019 to Mar 2020)…but we’re not 100% sure of this.

   Thank you for the great blog post!

   Reply ↓

   • I can dig some of it up. Can you compile .NET code? When using Visual Studio Code instead of .NET we need to use .NET Core and I think I have that as well somewhere.

     Reply ↓

2. I think I was able to reproduce using a tight loop calling System.Data.SqlClient.SqlConnection within PowerShell. I will reapply the latest Server CU and give it a whirl.

   Thanks again for your great post!

   Reply ↓

   • Yeh, that will do the job as well, in the end, we looped SQLCommand in a DataReader hit the issue every X hundred times. Good luck. Happy it helped someone out.

     Reply ↓

3. Fantastic post – thanks very much!

   Ron – would you be willing to share the PowerShell script?

   Thanks in advance!

   Andrew de Mos

   Reply ↓

   • Here is some .NET core code for inspiration. In PowerShell you can do the same. I had to mask connectionstrings etc. But you get the drift.
     using System;
     using System.Collections.Generic;
     using System.Configuration;
     using System.Data.SqlClient;
     using System.Diagnostics;
     using System.IO;
     using System.Linq;
     using System.Net;
     using System.Text;
     using System.Threading;
     using System.Threading.Tasks;

     namespace TLS-TEST
     {
     class Program
     {
     static void Main(string[] args)
     {
     int count = 0;
     string connectionString = “CONNECTIONSTRINGe”;
     //connectionString = ConfigurationManager.ConnectionStrings[“DATA”].ConnectionString;

             //Console.WriteLine("Attempt to connect");
     
             //
             // Summary:
             //     Specifies the Secure Socket Layer (SSL) 3.0 security protocol.
             // Ssl3 = 48,
             //
             // Summary:
             //     Specifies the Transport Layer Security (TLS) 1.0 security protocol.
             // Tls = 192,
             //
             // Summary:
             //     Specifies the Transport Layer Security (TLS) 1.1 security protocol.
             // Tls11 = 768,
             //
             // Summary:
             //     Specifies the Transport Layer Security (TLS) 1.2 security protocol.
             // Tls12 = 3072
             //System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11;
             //Console.WriteLine("security protocol: " + System.Net.ServicePointManager.SecurityProtocol);
             while (true)
             {
     
     
                 try
                 {
                     using (SqlConnection con = new SqlConnection(connectionString))
                     {
                         con.Open();
     
                         using (SqlCommand command = new SqlCommand("SELECT top(1) * from sys.tables", con))
                         {
                             using (SqlDataReader reader = command.ExecuteReader())
                             {
                                 while (reader.Read())
                                 {
                                     //Console.WriteLine("Result: {0}", reader.GetString(0));
                                     var output = reader.GetString(0);
                                     Console.WriteLine (output);
                                     count++;
                                     string filename = String.Format("{0:HH-mm-ss.ffff}__{1}{2}.txt", DateTime.Now, "Success", count);
     
                                     using (StreamWriter sw = File.CreateText(Path.Combine(@"c:\temp\TLSConnectionTest", filename)))
                                     {
                                         sw.WriteLine("It went well");
                                         sw.WriteLine("Date : " + DateTime.Now.ToString());
                                         sw.WriteLine("security protocol: " + System.Net.ServicePointManager.SecurityProtocol);
                                     }                                  
                                 }
                             }                            
                         }                      
                     }
                 }
     
                 catch (Exception ex)
                 {
                     count++;
                     string filename = String.Format("{0:HH-mm-ss.ffff}__{1}{2}.txt", DateTime.Now, "exception", count);
     
                     using (StreamWriter sw = File.CreateText(Path.Combine(@"c:\temp\TLSConnectionTest", filename)))
                     {
                         sw.WriteLine("EXCEPTION!");
                         sw.WriteLine("Date : " + DateTime.Now.ToString());
                         sw.WriteLine("message:" + ex.Message);
                         sw.WriteLine("stacktrace:" + ex.StackTrace);
                         sw.WriteLine("security protocol: " + System.Net.ServicePointManager.SecurityProtocol);
                     }
                 }
             }
         }
     }
     

     }

     Reply ↓

4. Similarly here’s the essential bits I used:

   $i = 0
   while($i -lt 99999)
   {
   $i++
   try
   {
   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
   $SqlConnection.ConnectionString = “Server=;Database=master;Integrated Security=true;Pooling=false;”
   $SqlConnection.Open();
   $SqlConnection.Close()
   }
   catch
   {
   write-host $($_ | Out-String) -foregroundcolor “red” -backgroundcolor “black”
   write-host $($_.Exception|format-list -force|out-string) -foregroundcolor “red” -backgroundcolor “black”
   }
   }

   Reply ↓

5. Gentlemen – thanks very much! I will let you know how it goes.

   Andrew de Mos

   Reply ↓

   • Happy to help 🙂

     Reply ↓

6. Just wanted to give a big thank you for taking your time to write about this.
   We’re facing the exact same problem (symptoms) and you’re pushing us in the right direction.
   Thanks and keep up the good work! 🙂
   /Charles

   Reply ↓

   • You’re most welcome. Please report back with your success in this as I am in contact with MSFT about looking into this.

     Reply ↓

7. We were unable to reproduce the issue using the PowerShell script (I suspect that the issue has to do with image activation), but we proceeded with disabling the ciphers:

   TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
   TLS_DHE_RSA_WITH_AES_128_GCM_SHA256

   …on one of our utility servers that access a SQL 2019 database server (running on Windows Server 2019 Standard), and the issue disappeared (we monitored for a full week)!

   This morning, we disabled the two ciphers on three other servers – and will monitor the results.

   Thanks again for your help!

   Andrew de Mos

   Reply ↓

8. Very well explained Ron. We are experiencing same issue in our environment and your post helped us to look in right direction. Appreciate it!

   Reply ↓

9. Regard error: “A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)”

   I found this article which mentions the TLS_DHE cipher suite on Windows 2012 R2 has issues account for leading zeros on the MAC when calculating keys. And the TLS client and server need to calculate keys exactly the same way otherwise they get different results.

   The article mentions Windows 2012 R2 does not have the patch, but Windows 2016 and up do.
   I think this is my problem. And your solution should fix it. I’ll see after my next reboot.

   https://docs.microsoft.com/en-us/troubleshoot/windows-server/identity/apps-forcibly-closed-tls-connection-errors

   Reply ↓

   • Thx for the feedback. Do let us know what the end result is for you. It sure seems like you should be ok after implementing the workaround.

     Reply ↓

10. Pingback: Offline Azure Devops Windows 2012 R2 build server with failing builds - Working Hard In ITWorking Hard In IT

11. Lovely post, thank you for the in-depth article. I’m recently facing a issue similar to this, but it’s intermittent after using what we call ‘Gateway’ console applications, even Xamarin IOS/Android applications – which all use later TLS 1.2 consistently. What we are finding is that our Server 2016 (even with the REG Edit settings for Client/Server) set to disabled anything but TLS 1.2, to somehow come back with a Error Event in Event Log that says ”An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The TLS connection request has failed.”

    This is bizare, and i have a support request with Azure for our Cloud Service (Classic) that we see this on, but my question is: “Have you ever seen a similar issue like your article explains, generated by a Server that seems to turn off/on intermittently with Schannel Error Log Event created ?

    This seems bizare….

    Reply ↓

    • My first idea is to to check the cipher suites on the Windows Box and the client, ensure they have a common one.
      https://www.nartac.com/Products/IISCrypto/FAQ/what-is-the-windows-default-cipher-suite-order

      In the registry, you can add/remove/enable/disable and change the order.

      Reply ↓

This site uses Akismet to reduce spam. Learn how your comment data is processed.