But then an error occurred during the pre login handshake

First published on MSDN on Jul 09, 2015

This blog is regarding one of most commonly faced issues that you may receive when connecting to the SQL Server. Mostly you may run into this issue after some improper Windows security update (say

KB2655992

in my case) or improper application of Poodle security fix.

ISSUE DESCRIPTION FROM SQL CONNECTIVITY STANDPOINT:

When we try to connect to the SQL Server instance using the SQL Server Management Studio, it may fail with the following error message,

Error:

TITLE: Connect to Server

——————————

Cannot connect to <mySQLServer>.

——————————

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 — No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkI…

——————————

BUTTONS:

OK

——————————

Using a UDL file, we saw the following error message,

—————————

Microsoft Data Link Error

—————————

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

—————————

OK

—————————

Note:

Forcing the connection to use any other protocol like TCP, Named Pipes & Shared Memory also throws the same error message. The Dedicated administrator connection (DAC) was also throwing a “Login timeout expired.” error.

OUR FINDINGS & INFERENCE:

There are numerous reasons on why you may witness these error messages. But usually, if you see these 2 specific error messages from SQL Server Management Studio & UDL file. Then it is worth checking the below settings.

As per the error message received, “…


an error occurred during the pre-login handshake



.”

and “


[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error

.”

means that the client application was able to complete the TCP 3-way handshake properly (hence you notice “


A connection was successfully established with the server


”), but during the


pre-login handshake,


the client application checks with the SQL Server on the TDS protocol version to be used henceforth for the communication, the login passed by the client application (Windows authenticated login or SQL Authenticated Login), whether there is any client-side or SQL Server connection encryption using SSL certificates or TLS etc. If the SQL Server doesn’t respond to this request from the client in a timely fashion or fails to respond due to any internal machine-level issues, we end-up at this particular error message. (wiz. “…


an error occurred during the pre-login handshake”


)

These error messages are thrown from the actual SQL Server drivers / providers that are used to establish the connection to the SQL server. E.g (OLE DB provider for SQL Server / SQL Server Native Client etc.). So different SQL Drivers/ Providers throws different error messages for the same issue. When we tried the connection from the UDL file, we see a different error message for this reason, but this error message was more straightforward.

Error:

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

Hence, we directly jumped to the SCHANNEL registry hive to check the values. Wiz.

Target hive:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL

CAUSE:

By default, you may not find the below registry keys, which is completely fine. But  in my case, when we checked the values for these registry keys on the target SQL Server, the following were the values.

i)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsSSL 2.0Server

DisabledByDefault

was set to 1

Enabled

was set to 0

ii)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsSSL 3.0Server

DisabledByDefault

was set to 1

Enabled

was set to 0

iii)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0 Server

DisabledByDefault

was set to 0

Enabled

was set to 0

Based on these values, we figured-out that none of the security provider protocols were enabled. We confirmed that all SSL 2.0, SSL 3.0 & TLS 1.0 were disabled and this is not an ideal scenario.

RESOLUTION:

We enabled the TLS 1.0 protocol by setting the following value.

Under,

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0 Server

Set

Enabled

to

1

Rebooted the SQL Server machine for the changes to reflect.

After the successful reboot, we were able to connect to the SQL Server locally without any issues. Also tested the connection from a remote server machine and confirmed that the connections to the SQL Server worked as expected without any issues. SQL server was just a victim here like any other application that uses windows security providers and since all the security provider’s protocols were disabled, the SQL Server wasn’t able to accept any new connection request.

Please drop in your comments or connect with Microsoft BI-ONE CSS team if you are still encountering the same issue even after performing the above steps.

Happy troubleshooting!!!!

Author: Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft

Reviewed by: Sunil Kumar B.S, Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft.

I am getting following error when i am trying to connect Production DB from Local Environment.

I was able to connect Production DB before, but suddenly i am getting following error, any idea?

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 — The handle is invalid.)

I was trying to run asp.net website in local PC, which has connection string of Production DB, following is stack trace for error I am getting in local environment.

>    at MyWebsiteDAL.clsForumQuestion.SelectAll(Int32 CurrentPageIndex, Int32 PageSize) in D:EDriveMy WebSitesMyWebsiteMyWebsiteMyWebsiteDALclsForumQuestion.cs:line 821
       at CodeConnect.Default.Page_Load(Object sender, EventArgs e) in D:EDriveMy WebSitesMyWebsiteMyWebsiteMyWebsiteDefault.aspx.cs:line 100
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Any idea what might have gone wrong here?

Describe the bug

After I update the Microsoft.Data.SqlClient from 3.0.1 to 4.0.1 when I try to access the database I’m getting

Exception message:
Stack trace:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
 ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
   at System.Net.Security.SslStream.SendAuthResetSignal(ProtocolToken message, ExceptionDispatchInfo exception)
   at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
   at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](TIOAdapter adapter, Boolean receiveFirst, Byte[] reAuthenticationData, Boolean isApm)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:51a7429a-eee3-4ee4-802e-be6c50c1129b
Error Number:-2146893019,State:0,Class:20

Expected behavior

Connect to database

Further technical details

Microsoft.Data.SqlClient version: 4.0.1
.NET target: .NET 6.0 on MacOS
SQL Server version: Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) — 15.0.4188.2 (X64) Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS)
Operating system: Docker Linux

Additional context
I’m running EF Core 6.0.1 migrations

But then an error occurred during the pre login handshake ssl provider

Question

Today I could not log into my local SQL Server 2012 instance with the following error message

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

provider: SSL Provider, error: 0 — An internal error occurred. (Microsoft SQL Server, Error: -2146893792)

I found many similar questions here and on the forums, but nothing helped. Please notice that in my case it just says

• There are no errors in Event Viewer;
• I don’t use Encrypted Connections;
• In SQL Server Configuration Manager, Force Protocol Entryption is set to False , Trust Server Certificate is set to Yes (Originally it was No , but in both cases it didn’t work);
• I CAN connect to SQL Server using UDL;
• I tried to re-install SQL Server with all related components;
• Tried to uninstall .NET 4.5;
• In SQL Management Studio, in connection properties, Encrypt Connection is unchecked.

It worked fine yesterday, I have not installed any software since then.

Источник

Applications experience forcibly closed TLS connection errors when connecting SQL Servers in Windows

This article helps fix an issue that occurs when an application tries to open a connection to a SQL Server.

Applies to: В Windows Server 2019, Windows Server 2016
Original KB number: В 4557473

Symptoms

When an application tries to open a connection to a SQL Server, one of the following error messages is displayed:

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

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 — An existing connection was forcibly closed by the remote host.)

If you enabled SChannel logging on the Server, you’ll receive Event ID 36888 (A Fatal Alert was generated) when the issue occurs.

• Depending on the provider or driver that you’re using, the error message may slightly vary.
• This issue also occurs when an application running on Windows Server 2012 R2 tries to connect to SQL Server running on Windows Server 2019.
• Other client-server applications may encounter a similar issue.

Cause

Windows 10, version 1511 and later versions of Windows, including Window Server 2016 or Windows 10, version 1607 that has updates released on Feb 25thor later updates installed, contains a leading zero update. Meanwhile, all Windows versions that released before that don’t contain the leading zero updates.

The TLS client and server need to calculate keys exactly the same way otherwise they get different results. TLS connections randomly fail if leading zeros are computed differently by the TLS client and TLS Servers.

When a Diffie-Hellman key exchange group has leading zeros, unpatched computers may incorrectly compute the mac by not accounting for the padded zeros. This issue is typically seen when interacting with non-Windows-based crypto implementations and can cause intermittent negotiation failures.

The error messages are returned when the secure TLS handshake is negotiated between the client and the server by using TLS_DHE cipher suite. The use of one of the affected cipher suites can be identified in the «Server Hello» packet. For more information, see the network snippet in the «More information» section.

Resolution

To fix this issue, make sure that both the client and server involved in a connection are running Windows that have the leading zero fixes for TLS_DHE installed. It’s recommended to install the updates since they enhance the conformance to TLS_DHE specifications.

The following list the operating system version according to the updates that are installed.

Windows versions that contain the leading zero fixes for TLS_DHE

• Windows Server 2016, version 1607
  • KB 4537806: February 25, 2020-KB4537806 (OS Build 14393.3542)
  • KB 4540670: March 10, 2020-KB4540670 (OS Build 14393.3564)
  • Updates that supersede KB4537806 and KB4540670 for the respective OS versions
• Windows Server 2019 RTM and later versions.
• Windows 10, version 1511, and later versions of Windows 10 (see release history)

Windows versions that don’t contain the leading zero fixes for TLS_DHE

• Windows Server 2016, version 1607 servers that don’t have the patches KB 4537806 and KB 4540670 applied.
• Windows 10, version 1507
• Windows 8.1
• Windows 7
• Windows Server 2012 R2 and earlier versions of Windows Server

Workaround

If you can’t update Windows, as a workaround, you can disable the TLS_DHE ciphers by using one of the two methods.

Using Group Policy

TLS_DHE_* ciphers can be disabled by using Group Policy. Refer to Prioritizing Schannel Cipher Suites to configure the «SSL Cipher Suite Order» group policy.

Policy URL: Computer Configuration -> Administrative Templates -> Network -> SSL Configuration Settings
Policy Setting: SSL Cipher Suite Order setting.​

Using a PowerShell script

More information

You can confirm that you’re encountering this issue during the connection establishment. When the issue occurs, you can see the following sequence in the network trace on the server.

Examining the Server Hello packet to see the cipher suite being used:

Reference

For more information, see the following articles:

Источник

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. #1479

Describe the bug

After I update the Microsoft.Data.SqlClient from 3.0.1 to 4.0.1 when I try to access the database I’m getting

Expected behavior

Connect to database

Further technical details

Microsoft.Data.SqlClient version: 4.0.1
.NET target: .NET 6.0 on MacOS
SQL Server version: Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) — 15.0.4188.2 (X64) Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS)
Operating system: Docker Linux

Additional context
I’m running EF Core 6.0.1 migrations

The text was updated successfully, but these errors were encountered:

Could be related with #1464

Have you tried to set TrustServerCertificate=True in your connection string ?

@lillo42 have your tried adding Encrypt=false to your connection string? or as mentioned above TrustServerCertificate=True

I had the same issue, as a workaround I suggest switching to System.Data.SqlClient, it works as expected

@JRahnama and @alaincroisetiere when I add TrustServerCertificate=True or Encrypt=false works, why I need to added it now?

I had the same issue, as a workaround I suggest switching to System.Data.SqlClient, it works as expected

System.Data.SqlClient is in servicing mode and is not updating on regular basis, but for addressing security issues and important updates. We suggest using Microsoft.Data.SqlClient as active ADO.NET library which gets updated and implements new features.

By changing this behavior, two issues has been addressed :

Default Encrypt to True. This is for security. Similar to http/https, if the client starts with allowing non-encrypted connections, it will always be susceptible to MITM attacks. Even if the server is configured to require encryption, there can be a MITM altering the server’s response to say it doesn’t require encryption. The MITM can then proxy the connection. client MITM server = the connection is compromised.

Security has been encouraging us for years to change the default behavior of client drivers to be secure by default and we have resisted, knowing that it is a breaking change for most users. It’s easy enough for developers to add Encrypt = false to all their connection strings, if they need to. We just want to make sure they understand the choice they are making and they are making it deliberately. With cloud computing becoming more and more common, it’s not safe to leave the default value of Encrypt equal to false.

The less-breaking, but still important, fix here is to ensure connections fail if the client does not have any encryption libraries available and either Encrypt = true or the server requires encryption. SqlClient + native SNI is the only MS driver we’ve found that will successfully connect in that scenario.

Источник

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 — Encryption(ssl/tls) handshake failed) Mac OSX High Sierra 10.13.5 #26388

In PowerShell I try to test an SQL connection from a Mac OSX 10.13.5. DotNet Core and PowerShell were installed through homebrew.

And I get this error:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 — Encryption(ssl/tls) handshake failed)

dotnet —version : 2.1.300
$PSVersionTable

The text was updated successfully, but these errors were encountered:

Hi @corretge your issue seems to be similar to #1048 and #1090.
Looks like this is resolved in the latest .NET Core 2.1. Could you please provide the output of dotnet —info ?

I am seeing the same issue running .NET Core 2.1.300.

@corretge @moconchobhair
As confirmed I was able to successfully connect to a local MSSQLServer hosted on Windows 10 as seen from the output below using PowerShell Core version v6.1.0-preview.2 and .NET Core version 2.1.300 on Mac OSX 10.13.5

Could you kindly download and install the .NET Core SDK 2.1.300 from the link above and try again.

Mac OSX version

PowerShell Core Script

PowerShell Core script output

PowerShell Core version

.NET Core version and info

@corretge @moconchobhair Did you get a chance to test the connection with the latest version of .NET Core SDK 2.1.300 ?

I am still seeing this issue on High Sierra 10.13.5

The new version of sql ops studio I downloaded fixed the issue, but I can’t seem to get a .net core app to use Entity Framework to connect to a data (the same error as above and the same error I was seeing in Sql Ops Studio before I upgraded). I’ve tried updating all my packages in my proj file to the absolute latest nuget version with no luck:

I have the same sort of issue. With a plain vanilla project (.net core 2.1) deployed to a CentOS Box with entity framework I receive the same stacktraces in my application as soon as it is trying to connect to SQL server (it’s on the same box). I tried localhost, 127.0.0.1, domain, but all give the same error. Connecting from Windows with SQL Server Management studio works fine, so this must be an issue in the tooling / runtime available for linux or mac osx

@corretge @moconchobhair Do you still see this issue with PowerShell Core as initially mentioned ?

@mcblakeb Could you kindly share a repro for the issue, I am unable to repro this in my environment even
with Entity Framework.

@las3r I was able to successfully connect using both localhost and 127.0.0.1 with:

• SQL Server hosted on Windows machine from both Mac OSX and Linux client.
• SQL Server hosted on Linux machine from both Mac OSX and Linux client.
• SQL Server hosted on Docker on Linux machine from both Mac OSX and Linux client.

Could you kindly share:

1. A repro of the issue, which includes the Connection String being used.
2. Output of the command netstat -ano | grep «1433» if using Linux OR netstat -ano | find «1433» if using Windows

Hi @keeratsingh the same issue on the CentOS 7

sdk 2.1.302 net core 2.1.2

Was problem with nuget v3 but fix DOTNET_SYSTEM_NET_HTTP_USESOCKETSHTTPHANDLER=false works.

Retrying ‘FindPackagesByIdAsync’ for source ‘https://api.nuget.org/v3-flatcontainer/microsoft.visualstudio.web.codegeneration.design/index.json’.
The SSL connection could not be established, see inner exception.
error:2006D002:BIO routines:BIO_new_file:system lib
Retrying ‘FindPackagesByIdAsync’ for source ‘https://api.nuget.org/v3-flatcontainer/microsoft.aspnetcore.all/index.json’.

System.Data.SqlClient.SqlException : A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 — Encryption(ssl/tls) handshake failed)
—- Interop+Crypto+OpenSslCryptographicException : error:2006D002:BIO routines:BIO_new_file:system lib
Stack Trace:
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

Hi @bartonjs, @saurabh500 mentioned you might be able to guide us in the right direction on how to tackle this issue. Any suggestions/ideas ?

Interop+Crypto+OpenSslCryptographicException : error:2006D002:BIO routines:BIO_new_file:system lib happening during a time when you would expect a certificate chain / trust decision means that there are files in /etc/ssl/ (or wherever the correct directory is for the distro) that are broken symlinks or they point to files the current user can’t read. This is/was #26294, which will be fixed in 2.1.3.

Workarounds mainly involve fixing the contents of the cert store directory.

Sorry @keeratsingh, I tested it but did not answer 😱

I have the same issue:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 — Encryption(ssl/tls) handshake failed)

dotnet version: 2.1.300

But now I can connect with the last version of SQL Operations Studio. Maybe this info helps.

@corretge @moconchobhair @mcblakeb
This issue is fixed by dotnet/corefx#30155 as suggested by Jeremy. Could you kindly download and confirm with the Latest .NET Core SDK 2.1.302 Build if the issue is resolved.

@keeratsingh It does indeed appear to be fixed after installing 2.1.302 on Mac. I had to update all projects over use netcoreapp2.1 as well.

@keeratsingh I am able to successfully connect to all of my databases. Thank you so much!

Thank you for confirmation. I will go ahead and close this issue. Feel free to reopen if the issue still exists.

@keeratsingh the issue still exists.
dotnet —info

.NET Core SDK (reflecting any global.json):
Version: 2.1.302
Commit: 9048955601

Runtime Environment:
OS Name: centos
OS Version: 7
OS Platform: Linux
RID: centos.7-x64
Base Path: /usr/share/dotnet/sdk/2.1.302/

Host (useful for support):
Version: 2.1.2
Commit: 811c3ce6c0

.NET Core SDKs installed:
2.1.302 [/usr/share/dotnet/sdk]

Источник

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

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

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

SQL 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 utilised, 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.