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

Previously we had our dev server running Windows 2012 R2 and SQL Server 2017 and we connected fine with SQL Data Compare and SQL Compare to the server.

We recently redid that server with Windows Server 2019 and SQL Server 2019, since this move I can not get SQL Data Compare and SQL Compare to work. It will still connected to our other SQL Server which is still running on another Windows 2012R2 / SQL Server 2017 box and read those databases fine when in the new project window, but when I set the target DB to our newer 2019 server it throws an error

"A Connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host)"

When I look on the server there are SChannel errors to match

"An unknown 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."

Now this is * ONLY * happening that I can tell in SQL Compare and SQL Data compare (the only two stand alone products I use)

I can connect to this database server via SSMS, Azure Data Studio, Visual Studio SSDT, and SQLCMD just fine. SQL Prompt also works inside of SSMS just fine talking to this database server.

Why in the world am I having this issue with SQL Compare / Data compare only? It worked great before the upgrade.

Answers

  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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?
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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.......
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    version that is having issues is version 14.5.1.18536 for SQL Compare and the same version for SQL Data Compare
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    I just reverted SQL Compare back to version 13.8.0.12703 and it works again....... something isn't right with the latest version
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    and just to add to the what the of this somehow going back to 13.8.0 triggers and easter egg that plays asteroids?!

  • 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.



    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    Also, that doesn't work either

  • Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • BlueBSHBlueBSH Posts: 25 Bronze 2
    edited August 27, 2021 2:16PM
    DanC said:
    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
  • Is the connection
    - on the local machine
    - over the LAN
    - over the internet?
    Software developer
    Redgate Software
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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 
  • 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?
    Software developer
    Redgate Software
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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.

  • We have updated the connection library (Microsoft.Data.SqlClient) since SQL Compare 14.5.1, so it's worth a shot.
    Software developer
    Redgate Software
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    Same error in 14.6.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?
    Software developer
    Redgate Software
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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
  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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
  • Hi @BlueBSH

    Given your latest update, how would you like to handle this, seeing as it seems like an environmental issue


    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • BlueBSHBlueBSH Posts: 25 Bronze 2
    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
  • Hi @BlueBSH

    Ah, that's interesting, glad you were able to narrow down the root of the issue!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

Sign In or Register to comment.