Microsoft.Data.SqlClient.SqlException (0x80131904): 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)

[jonfen]

Linux Version 1.19.0 forces TLS1.2 and therefore can't connect to Windows Server 2012 Standard? Related to: #1727

• Azure Data Studio Version:

Version: 1.19.0 Commit: 4095037f2578c23033867e611e82c13de114ca5a Date: 2020-06-11T21:58:44.841Z VS Code: 1.46.0 Electron: 7.2.4 Chrome: 78.0.3904.130 Node.js: 12.8.1 V8: 7.8.279.23-electron.0 OS: Linux x64 5.4.0-40-generic

Steps to Reproduce:

db.domain.com Windows Server 2019 Standard (SQL Server 13.0.4259.0) dbd.domain.com Windows Server 2012 Standard (SQL Server 13.0.4001.0)

1. Using SQL authentication I can connect to db.domain.com, but not dbd.domain.com from a Linux (Ubuntu 20.04) install of azuredatastudio 1.19.0 However I am able to connect to both of them using a windows install of azuredatastudio 1.19.0

[jonfen]

Attempting with the insider build I get more information:

Version: 1.20.0-insider Commit: cbf3cd7445b8471f32b998cefd9281070afe2217 Date: 2020-07-14T05:32:24.361Z VS Code: 1.46.0 Electron: 7.3.2 Chrome: 78.0.3904.130 Node.js: 12.8.1 V8: 7.8.279.23-electron.0 OS: Linux x64 5.4.0-40-generic

Microsoft.Data.SqlClient.SqlException (0x80131904): 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)
 ---> System.IO.EndOfStreamException: End of stream reached
   at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.ReadInternal(Byte[] buffer, Int32 offset, Int32 count, CancellationToken token, Boolean async)
   at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.Net.FixedSizeReader.ReadPacket(Stream transport, Byte[] buffer, Int32 offset, Int32 count)
   at System.Net.Security.SslStream.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslStream.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslStream.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslStream.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslStream.ProcessAuthentication(LazyAsyncResult lazyResult, CancellationToken cancellationToken)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at Microsoft.Data.SqlClient.SNI.SNIProxy.EnableSsl(SNIHandle handle, UInt32 options)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass45_0.<TryGetConnection>b__1(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 314
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 549
ClientConnectionId:dcd2f520-edbe-45c4-9ffb-475f58fd449f

[tevosouza]

I faced this issue on debian buster, after few days I've finally solved the problem by upgrading to openssl to 1.1.1h

wget https://www.openssl.org/source/openssl-1.1.1h.tar.gz
tar -zxfv openssl-1.1.1h.tar.gz
cd openssl-1.1.1h
./config

# install dependencies if you not did before
sudo apt-get install make gcc
sudo make install

#create symlink to new openssl (if already exists delete it)
sudo ln -s /usr/local/bin/openssl /usr/bin/openssl 

# update symlinks
sudo ldconfig

# run verification
$ openssl version
OpenSSL 1.1.1h  22 Sep 2020

I hope that help others with the same problem

=)

[gacalves]

I faced this issue on debian buster, after few days I've finally solved the problem by upgrading to openssl to 1.1.1h

wget https://www.openssl.org/source/openssl-1.1.1h.tar.gz
tar -zxfv openssl-1.1.1h.tar.gz
cd openssl-1.1.1h
./config

# install dependencies if you not did before
sudo apt-get install make gcc
sudo make install

#create symlink to new openssl (if already exists delete it)
sudo ln -s /usr/local/bin/openssl /usr/bin/openssl 

# update symlinks
sudo ldconfig

# run verification
$ openssl version
OpenSSL 1.1.1h  22 Sep 2020

I hope that help others with the same problem

=)

Thanks @tevosouza , works on Ubuntu 20.04.

[DarkMike-ru]

Don't install openssl from source Problem in openssl config. Debian 10 (and Ubuntu, I think) has this setting (file /etc/ssl/openssl.cnf, at the end): CipherString = DEFAULT@SECLEVEL=2 Just change it to CipherString = DEFAULT@SECLEVEL=1 and connection work

[tevosouza]

Hi @DarkMike-ru , Thank you a lot, i'll try it when i install new system! Why not update openssl, is there any breaking changes or any issue with latest version ?

I've checked the openssl config file and value was not changed DEFAULT@SECLEVEL=2 and connection works like a charm, maybe it can be a little bug?

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT@SECLEVEL=2

[DarkMike-ru]

If your install from source your lose security update for this part of system. It's not a solution. And install of new version is not needed - problem only in config. Please check where your config reside after manual installation. I think config now in /usr/local/ssl/openssl.cnf

[embix]

When dealing with old/unpatches instances (like SQL Server 2008R2, BTW: ops already has a ticket to update it) we had success with:

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=1

[hongquan]

For Ubuntu 20.10, you can follow this guide to manually add the config lines.

[brunodorati]

I faced this issue on debian buster, after few days I've finally solved the problem by upgrading to openssl to 1.1.1h

wget https://www.openssl.org/source/openssl-1.1.1h.tar.gz
tar -zxfv openssl-1.1.1h.tar.gz
cd openssl-1.1.1h
./config

# install dependencies if you not did before
sudo apt-get install make gcc
sudo make install

#create symlink to new openssl (if already exists delete it)
sudo ln -s /usr/local/bin/openssl /usr/bin/openssl 

# update symlinks
sudo ldconfig

# run verification
$ openssl version
OpenSSL 1.1.1h  22 Sep 2020

I hope that help others with the same problem

=)

Works for me! Tanks.

[plmosqueda]

I can't find the seclevel option in Fedora 34 openssl config file. I think is due to the changes in crypto-policies. They recoemdend in the wiki chage the default to f32 or legacy, in my case legacy works

update-crypto-policies --set LEGACY (in terminal as root)

This is the original article https://fedoraproject.org/wiki/Changes/StrongCryptoSettings2

[dhunt84971]

For me to get @embix solution to work on an instance of SQL 2014 SP1 v12.0.4100 it was also necessary to install the cumulative update CU13 from here:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=51186

This was from Azure Data Studio v1.32.0 on Ubuntu 20.04 (popos flavored). The SQL server was running on Windows 7 SP1.

[cbdp]

Adding this at the top of /etc/ssl/openssl.cnf openssl_conf = openssl_init

And this at the bottom:

[openssl_init]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT@SECLEVEL=1

Worked for me. Thanks @DarkMike-ru

[jamtycle]

Adding this at the top of /etc/ssl/openssl.cnf openssl_conf = openssl_init

And this at the bottom:

[openssl_init]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT@SECLEVEL=1

Worked for me. Thanks @DarkMike-ru

Worked on ManjaroLinux!

[endersonmaia]

I'm using Ubuntu 22.04 LTS and Azure Data Studio 1.37.0

I already testes the solutions proposed here i this thread, but they didn't worked.

Azure Data Studio

Version: 1.37.0
Commit: d904740d93d7df76a0ba361f20e4351813b57645
Date: 2022-06-14T00:52:49.854Z
VS Code: 1.59.0
Electron: 13.6.6
Chrome: 91.0.4472.164
Node.js: 14.16.0
V8: 9.1.269.39-electron.0
OS: Linux x64 5.14.0-1032-oem

SQL Server Version

Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Error

Microsoft.Data.SqlClient.SqlException (0x80131904): 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)
 ---> System.IO.IOException:  Received an unexpected EOF or 0 bytes from the transport stream.
   at System.Net.Security.SslStream.ReceiveBlobAsync[TIOAdapter](TIOAdapter adapter)
   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 System.Net.Security.SslStream.AuthenticateAsClient(String targetHost)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at Microsoft.Data.SqlClient.SNI.SNIProxy.EnableSsl(SNIHandle handle, UInt32 options)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass48_0.<CreateReplaceConnectionContinuation>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 312
--- End of stack trace from previous location ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 637
ClientConnectionId:d0b7e8e5-f3ed-4d2f-8a5c-f04bd0f60543

[sebapolanco]

Don't install openssl from source Problem in openssl config. Debian 10 (and Ubuntu, I think) has this setting (file /etc/ssl/openssl.cnf, at the end): CipherString = DEFAULT@SECLEVEL=2 Just change it to CipherString = DEFAULT@SECLEVEL=1 and connection work

Debian 11 user here, this solve the problem

[gumbarros]

Anyone solved on Pop!_OS 22.04? This error happens also on Rider 2022 but not on DBeaver CE. I also using OpenVPN.

[plmosqueda]

Anyone solved on Pop!_OS 22.04? This error happens also on Rider 2022 but not on DBeaver CE. I also using OpenVPN.

The solution works on debian, I tried ubuntu with no Luck. It happens to me when i try to connect to a database via Fortinet vpn but work great on a hamachi vpn. I s not a server problem because the connection works great on fedora, rhel or Arch. Please some one help us XDDD

[cheenamalhotra]

@plmosqueda @gumbarros

Starting with .NET 5 onwards, default cipher suites have changed and have caused issues to client apps. Since ADS uses .NET 6 this is likely to cause impact. Would recommend you to try guidelines from here: Default TLS cipher suites for .NET on Linux

[gumbarros]

@cheenamalhotra tried editing my sslconf with the recommended action but still with the error 😢

openssl_conf = default_conf

///...

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256

[cheenamalhotra]

Could you troubleshoot possibilities these too as these are mostly observed issues? SqlClient Troubleshooting Guide

Most commonly observed problem is lack of TLS 1.2 support on target server as Linux clients are starting to limit using anything below it, leading to handshake issues.

[gumbarros]

@cheenamalhotra also tried

MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=1

and no success.

I restarted my machine after every edit on sslconf.

[cheenamalhotra]

Can you try this instead as TLSv1.2 should be the one enabled on OS.

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT@SECLEVEL=1

You can also try to update all settings to both locations:

• /usr/lib/ssl/openssl.cnf
• /etc/ssl/openssl.cnf

[plmosqueda]

Can you try this instead as TLSv1.2 should be the one enabled on OS.

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT@SECLEVEL=1

You can also try to update all settings to both locations:

• /usr/lib/ssl/openssl.cnf
• /etc/ssl/openssl.cnf

Hi cheenamalhotra, thank you for your time investigating this. I tested in fresh Ubuntu install today and is not working. :-(

[shaojun]

Same application runnin good from my Windows 11 machine.

I'm in Ubuntu 20.04.5 LTS x64, this is my openssl version:

OpenSSL 1.1.1h  22 Sep 2020

I'm using .NET CORE 6 console application, System.Data.SqlClient 4.8.5 to connect to a MS SqlServer 2008 server, these are all my testings:

• With system default /usr/local/ssl/openssl.cnf this is the error I got instantly by ran my app:

  System.Data.SqlClient.SqlException (0x80131904): 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) ---> System.IO.EndOfStreamException: End of stream reached

• Adding SECLEVEL=1 put below on top:

  openssl_conf = openssl_init

  put below on bottom:

  [openssl_init]
  ssl_conf = ssl_sect
  
  [ssl_sect]
  system_default = system_default_sect
  
  [system_default_sect]
  CipherString = DEFAULT@SECLEVEL=1

  this time I got with the app stuck over 10 seconds:

  System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=170; handshake=223; [Login] initialization=2; authentication=8; [Post-Login] complete=14162; ---> System.ComponentModel.Win32Exception (258): Unknown error 258

• Adding extra MinProtocol = TLSv1.1 that say:

  [system_default_sect]
  MinProtocol = TLSv1.1
  CipherString = DEFAULT@SECLEVEL=1

  this time got instantly error:

  System.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: Authentication failed, see inner exception. ---> Interop+OpenSsl+SslException: SSL Handshake failed with OpenSSL error - SSL_ERROR_SSL. ---> Interop+Crypto+OpenSslCryptographicException: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol

• Adding extra MinProtocol = TLSv1 that say:

  [system_default_sect]
  MinProtocol = TLSv1
  CipherString = DEFAULT@SECLEVEL=1

  this time got error with stuck like over 10 seconds as above:

  Unhandled exception. System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=293; handshake=221; [Login] initialization=2; authentication=9; [Post-Login] complete=14153; ---> System.ComponentModel.Win32Exception (258): Unknown error 258

[cheenamalhotra]

Hi @shaojun

System.Data.SqlClient is not used by ADS and is not actively maintained. It's superseded by Microsoft.Data.SqlClient driver. Can you verify the same with Microsoft.Data.SqlClient?

[shaojun]

@cheenamalhotra thanks.

I've replaced the System.Data.SqlClient 4.8.5 to Microsoft.Data.SqlClient 5.1.0 in my client application, still could not connect to the Sql server 2008 instance, and this time both(by using System.Data.SqlClient 4.8.5, at least worked from Windows) failed at sqlconn.Open() from Windows and Linux.

From Windows, the error said:

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 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.) Source=Core Microsoft SqlClient Data Provider StackTrace: at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt) ... ... ... at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Program.

$(String[] args) in C:\Users\music\source\repos\playground\TestMsSqlServerConn\Program.cs:line 18

Inner Exception 1: Win32Exception: The certificate chain was issued by an authority that is not trusted.

from Linux, the error said:

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

[cheenamalhotra]

With Microsoft.Data.SqlClient, Encrypt is enabled by default, so these are expected if you don't have encryption setup on the server.

You can specify "TrustServerCertificate=true" (recommended) or "Encrypt=false" on the connection string to resolve these. For more info, please take a closer look: https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnectionstringbuilder.encrypt?view=sqlclient-dotnet-standard-5.1

[shaojun]

@cheenamalhotra thanks for the replying.

this time I set connStringBuilder.TrustServerCertificate = true;, and did the testing again as early, still the same behavior as using System.Data.SqlClient 4.8.5, either errors like:

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)

or

Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or...`

[nicolas-gonzalez-badamax]

For Ubuntu 20.10, you can follow this guide to manually add the config lines.

Following that guide proved useful for me on Trisquel 10 (which is debian based).
Its the same that other said,

Adding this at the top of /etc/ssl/openssl.cnf openssl_conf = openssl_init

And this at the bottom:

[openssl_init]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT@SECLEVEL=1

But with the exception that adding openssl_conf = openssl_init at the top, means adding it below the HOME statement. This is the top of my file: