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

BlueBSH

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.

BlueBSH

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

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

version that is having issues is version 14.5.1.18536 for SQL Compare and the same version for SQL Data Compare

BlueBSH

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

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

DanC

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.

BlueBSH

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

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

Also, that doesn't work either

DanC

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

BlueBSH

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

Mark R

Is the connection

- on the local machine

- over the LAN

- over the internet?

BlueBSH

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 

Mark R

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?

BlueBSH

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.

Mark R

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.

BlueBSH

Same error in 14.6.0

Mark R

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?

BlueBSH

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

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

DanC

Hi @BlueBSH

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

BlueBSH

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

DanC

Hi @BlueBSH

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