Certificate error connecting SQLCompare to a server after upgrading to SSMS 19

DBAJohnny

I am connected successfully in SSMS to an on-prem SQL Server with a windows account from an on-prem domain (which uses an internally-generated certificate).

After upgrading to SSMS 19, I'm getting this error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Microsoft's article on the subject https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19 says the fix is to to set the "encrypt/use encryption" flag on the connection string to No or Optional.

How do I do that?

Robyn

Hi, 

Thanks for your post. 

Just before we go any further, please could I ask which version you are using? The need to make these changes should have been fixed in v15.0.3

DBAJohnny

I am using Redgate SQL Compare 15.0.7.23280.  I have tried uninstalling and re-installing, but that didn't help.

Robyn

This is expected behavior because of MS changing the default of their product.

So the product is trying to connect to the server securely (over SSL) and it rejecting the offered internally-generated certificate.
 
There are three possible resolutions we'd suggest:

1. The MS one you've found online, it's easy but insecure, just add ;encrypt=false to the end of the connection string
2. Tell the tool to trust the server certificate using the checkbox of the same name (just as fast as the above option, but more secure)
3. Installed a cert on the server from an accredited certificate authority (slower, most secure and costs money)

DBAJohnny

Robyn -
Thanks!  I now see the "trust server certificate" checkbox, just as you wrote.  All's good now.
Johnny