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 Change Automation Powershell
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.
Pre v15 GUI:
Add ;Trust Server Certificate=true to the Server field on the Data Sources tab of the project
Command Line:
Add ;Trust Server Certificate=true on the Server1 or Server2 switch
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
Redgate Monitor
You need to enable the Trust Server Checkbox in the Connection Properties of the monitored server
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.