SSL Error on database enumeration after upgrade to Windows Server 2019 / SQL 2019

• 
  To add to this I just tried to connect with SQL Doc and it connected to this same server that is throwing an error with SQL Compare with no issues. I also tried SQL Data Generator and it connected and listed the databases just fine with no errors...
  
  The only problems are with SQL Compare and SQL Data compare, what is different about them?

  BlueBSH August 26, 2021 14:21

  - 0 +
• 
  I've now installed SQL Compare and SQL Datacompare locally on the server to test that and it still returns the same SSL Provider error.......

  BlueBSH August 26, 2021 19:16

  - 0 +
• 
  version that is having issues is version 14.5.1.18536 for SQL Compare and the same version for SQL Data Compare

  BlueBSH August 26, 2021 20:49

  - 0 +
• 
  I just reverted SQL Compare back to version 13.8.0.12703 and it works again....... something isn't right with the latest version

  BlueBSH August 26, 2021 20:52

  - 0 +
• 
  and just to add to the what the of this somehow going back to 13.8.0 triggers and easter egg that plays asteroids?!
  
  

  BlueBSH August 26, 2021 20:57

  - 0 +
• 
  Hi @BlueBSH
  
  This error came about due to the recent update on the Microsoft Data Connection client update and so now you will need to do the following:
  
  You can work around this by adding ";Trust Server Certificate=true" to the end of the server name in the connection dialog.
  
  
  
  

  DanC August 27, 2021 13:27

  - 0 +
• 

  DanC said:

  Hi @BlueBSH
  
  This error came about due to the recent update on the Microsoft Data Connection client update and so now you will need to do the following:
  
  You can work around this by adding ";Trust Server Certificate=true" to the end of the server name in the connection dialog.
  
  
  
  

  I'm sorry, but that is not a good solution.... our security policy forbids us from trusting certificates blindly.. our certificate is valid and signed by a CA it shouldn't need to be trusted

  BlueBSH August 27, 2021 13:29

  - 0 +
• 

  BlueBSH said:

  DanC said:

  Hi @BlueBSH
  
  This error came about due to the recent update on the Microsoft Data Connection client update and so now you will need to do the following:
  
  You can work around this by adding ";Trust Server Certificate=true" to the end of the server name in the connection dialog.
  
  
  
  

  I'm sorry, but that is not a good solution.... our security policy forbids us from trusting certificates blindly.. our certificate is valid and signed by a CA it shouldn't need to be trusted

  To make this more confusing why do I need to trust a server certificate on a server that does not have encryption enabled? There should be no SSL exchange happening if encryption isn't set up or enforced... I get this same problem on a system that has no SSL encryption so there is no cert to trust

  BlueBSH August 27, 2021 13:43

  - 0 +
• 
  Also, that doesn't work either
  
  

  BlueBSH August 27, 2021 13:47

  - 0 +
• 
  Hi @BlueBSH
  
  I suspect actually you may be hitting this, please see the following:
  
  https://docs.microsoft.com/en-us/troubleshoot/windows-server/identity/apps-forcibly-closed-tls-connection-errors
  

  DanC August 27, 2021 14:13

  - 0 +
• 

  DanC said:

  Hi @BlueBSH
  
  I suspect actually you may be hitting this, please see the following:
  
  https://docs.microsoft.com/en-us/troubleshoot/windows-server/identity/apps-forcibly-closed-tls-connection-errors
  

  I've already went down that KB article route... If that was the case then why does version 13.8 work of data compare? It would still be an issue in 13.8 if that was the case.. It also makes no sense why this is only affecting SQL compare and SQL Data compare and no other red-gate products on the latest releases

  BlueBSH August 27, 2021 14:15

  - 0 +
• 
  Is the connection

  - on the local machine

  - over the LAN

  - over the internet?
  

  Mark R August 27, 2021 15:06

  - 0 +
• 

  Mark R said:

  Is the connection

  - on the local machine

  - over the LAN

  - over the internet?
  

  Everything is local inside a windows domain on machines on the same domain
  
  I can even reproduce this locally on the server

  BlueBSH August 27, 2021 15:08

  - 0 +
• 
  Huh. If the same problem happens when on the server itself, it can't possibly be the issue from that Microsoft doc as that's about running things on *different* Windows versions!

  
  

  What authentication method are you using?
  

  Mark R August 27, 2021 15:43

  - 0 +
• 

  Mark R said:

  Huh. If the same problem happens when on the server itself, it can't possibly be the issue from that Microsoft doc as that's about running things on *different* Windows versions!

  
  

  What authentication method are you using?
  

  We use windows authentication only, server is set up to only allow that
  
  we also only use TCP/IP for connections.
  
  The 14.5 version will connect to our Server 2012R2 / SQL Server 2017 system with no issues, just this with 14.5 to our Sever 2019 / SQL 2019 box.

  BlueBSH August 27, 2021 15:45

  - 0 +
• 
  Could you try 14.6.0? https://download.red-gate.com/checkforupdates/SQLCompare/SQLCompare_14.6.0.19635.exe

  
  

  We have updated the connection library (Microsoft.Data.SqlClient) since SQL Compare 14.5.1, so it's worth a shot.
  

  Mark R August 27, 2021 15:57

  - 0 +
• 
  Same error in 14.6.0

  BlueBSH August 27, 2021 15:59

  - 0 +
• 

  BlueBSH said:

  To make this more confusing why do I need to trust a server certificate on a server that does not have encryption enabled? There should be no SSL exchange happening if encryption isn't set up or enforced... I get this same problem on a system that has no SSL encryption so there is no cert to trust

  As you're not expectingt the connection to be encrypted, could you try adding

  ;Encrypt=False

  to the server name?
  

  Mark R August 27, 2021 16:06

  - 0 +
• 

  Mark R said:

  BlueBSH said:

  To make this more confusing why do I need to trust a server certificate on a server that does not have encryption enabled? There should be no SSL exchange happening if encryption isn't set up or enforced... I get this same problem on a system that has no SSL encryption so there is no cert to trust

  As you're not expectingt the connection to be encrypted, could you try adding

  ;Encrypt=False

  to the server name?
  

  I've tried that also before posting this and no luck

  BlueBSH August 27, 2021 17:30

  - 0 +
• 
  Created a Windows Server 2019 VM and installed SQL 2017 then upgraded to SQL 2019 like this problem environment was, then tried to connect and it worked fine.. leading me to believe something is not correct on the server, but it's still odd that only SQL Compare and SQL Data compare have an issue and nothing else does

  BlueBSH August 28, 2021 15:33

  - 0 +
• 
  Hi @BlueBSH
  
  Given your latest update, how would you like to handle this, seeing as it seems like an environmental issue
  
  
  

  DanC August 31, 2021 10:47

  - 0 +
• 
  After a lot of testing and a lot of VM snapshots to isolate changes.... I traced this down to TLS 1.3 being enabled in windows server, if you remove the registry keys for TLS 1.3 it works, put them back error

  BlueBSH August 31, 2021 18:01

  - 0 +
• 
  Hi @BlueBSH
  
  Ah, that's interesting, glad you were able to narrow down the root of the issue!

  DanC September 01, 2021 08:29

  - 0 +

Add comment

Please sign in to leave a comment.