An error occurred during the pre-login handshake An error occurred during the pre-login handshake

An error occurred during the pre-login handshake

If you receive the error message:

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

It's usually caused by a Certificate Validation issue. You may resolve this issue by choosing to accept the fixing the provided cert by stating Trust Server Certificate = True when connecting.

Please see below how this can be defined in the following tools:

SQL Compare and SQL Data Compare

SQL Source Control

SQL Change Automation Powershell

Redgate Monitor

Please also consider Issue origin and technical considerations for possible ramification so that you can make an informed choice.

SQL Compare and SQL Data Compare

v15 and later

This functionality is provided in the interface akin to the SSMS format.

mceclip0.png

Pre v15 GUI:

Add ;Trust Server Certificate=true to the Server field on the Data Sources tab of the project

mceclip0.png

Command Line:

Add ;Trust Server Certificate=true on the Server1 or Server2 switch

mceclip1.png

SQL Source Control

You can set Trust Server Certificate to True in the SQL Server Management Studio's Connection Properties Options

SQL Change Automation Powershell

If using a Powershell script:

You just need to add ;Trust Server Certificate=true to your connection string, for example:

Invoke-DatabaseBuild -InputObject $ScriptsFolder -TemporaryDatabase "Server=SQL_Server;Trust Server Certificate=true;Database=Temp_DB"

If using an addon:

Check the Trust Server Certificate check box

mceclip2.png

Redgate Monitor

You need to enable the Trust Server Checkbox in the Connection Properties of the monitored server

mceclip3.png

Issue origin

Microsoft have changed the default behavior of their connection tools (source) so that all connections will be established with encryption. In the context of increasingly decentralized infrastructure, this is a reasonable security precaution, but may also catch people unaware. Microsoft listed it as a breaking change and we have also sought to do so.

One common cause of certificate errors with this change is that many SQL Servers have not explicitly been set up with a certificate and are instead using their self signed cert from when they were initially configured (which is automatically generated). This often fails to meet the security requirements.

Microsoft detail the specific interactions here.

There are three possible avenues for handling your connectivity, please discuss the options with your respective IT security team and DBAs to choose the option most suitable for your organization.

1. Disable encryption - the least secure option that was default until recently. If your SQL Server is configured to enforce encryption, your connection attempt will be rejected.

2. Trust Server Certificate - your connection will be encrypted, but depending on the certificate being utilized, it may not be as secure as desired. Please see the Microsoft documentation for the specific reason of the notification to judge the impact.

3. Issue a dedicated certificate from a trusted CA - if you are using a hosted provider, they should provide a means for you to obtain this certificate so that you can include it on your clients.