Azuredatastudio: 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.