Issue

I'm trying to connect to a SQL Server 2008 R2 (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)) from Xubuntu 22.04 and .NET 6.0:

var sqlConnection = new SqlConnection("Server=server;Database=MyDb;User Id=MyUser;Password=MyPassword;ENCRYPT=false;TrustServerCertificate=true");

When trying to use the connection I get this error:

 System.Data.SqlClient.SqlException : 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:0A000102:SSL routines::unsupported protocol

As you can see from the connection string I already tried Encrypt=false and TrustServerCertificate=true.

I also tried the solution from https://stackoverflow.com/a/72137669/90800:

sed -i 's/openssl_conf = openssl_init/#openssl_conf = openssl_init/g' /etc/ssl/openssl.cnf

I also tried this one:

MinProtocol=TLSv1 CipherString=DEFAULT@SECLEVEL=1 dotnet run

Update:

It works without modifications from a Windows client.

Update:

I changed /etc/ssl/openssl.cnf from this:

[system_default_sect]
CipherString = DEFAULT:@SECLEVEL=2

to this:

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

Now the error is:

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)

This

[system_default_sect]
CipherString = DEFAULT@SECLEVEL=1

Causes this:

Interop+OpenSsl+SslException
SSL Handshake failed with OpenSSL error - SSL_ERROR_SSL.
   at Interop.OpenSsl.DoSslHandshake(SafeSslHandle context, ReadOnlySpan`1 input, Byte[]& sendBuf, Int32& sendCount)
   at System.Net.Security.SslStreamPal.HandshakeInternal(SafeFreeCredentials credential, SafeDeleteSslContext& context, ReadOnlySpan`1 inputBuffer, Byte[]& outputBuffer, SslAuthenticationOptions sslAuthenticationOptions)

Interop+Crypto+OpenSslCryptographicException
error:0A0C0103:SSL routines::internal error
  Exception doesn't have a stacktrace

Update:

There must be an option that works because from the same machine I can connect to the database using DataGrip with the jTds driver.

Update:

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

Result:

Interop+OpenSsl+SslException
SSL Handshake failed with OpenSSL error - SSL_ERROR_SSL.
   at Interop.OpenSsl.DoSslHandshake(SafeSslHandle context, ReadOnlySpan`1 input, Byte[]& sendBuf, Int32& sendCount)
   at System.Net.Security.SslStreamPal.HandshakeInternal(SafeFreeCredentials credential, SafeDeleteSslContext& context, ReadOnlySpan`1 inputBuffer, Byte[]& outputBuffer, SslAuthenticationOptions sslAuthenticationOptions)


Interop+Crypto+OpenSslCryptographicException
error:0A000102:SSL routines::unsupported protocol
  Exception doesn't have a stacktrace

Update (from https://github.com/dotnet/SqlClient/issues/567):

openssl s_client -tls1 -connect server:1433
CONNECTED(00000003)

Update:

These changes have been made to the registry:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols]

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0]

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client]

"DisabledByDefault"=dword:00000001

"Enabled"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server]

"Enabled"=dword:00000000

"DisabledByDefault"=dword:00000001

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1]

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]

"Enabled"=dword:ffffffff

"DisabledByDefault"=dword:00000000

Update:

I also installed the root CA certificate like this:

sudo apt-get install -y ca-certificates
sudo cp local-ca.crt /usr/local/share/ca-certificates
sudo update-ca-certificates

Update:

More openssl output:

SSL_connect:before SSL initialization
>>> TLS 1.0, RecordHeader [length 0005]
    16 03 01 00 74
>>> TLS 1.1, Handshake [length 0074], ClientHello
write to 0x55971383cef0 [0x55971384e1b0] (121 bytes => 121 (0x79))
SSL_connect:SSLv3/TLS write client hello
read from 0x55971383cef0 [0x559713844f93] (5 bytes => -1)
SSL_connect:error in SSLv3/TLS write client hello
write:errno=104
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 121 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.1
    Cipher    : 0000
    Session-ID: 
    Session-ID-ctx: 
    Master-Key: 
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    Start Time: 1658171132
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
---
read from 0x55971383cef0 [0x559713798600] (8192 bytes => 0)

Solution

You need to update your SQL Server machine to use TLS 1.2. Both Windows and SQL Server must be updated, or it will not work.

For Windows itself, you are on Windows Server 2008 R2, which needs this update at a minimum, really you should update to the latest build. Ensure you set the relevant registry settings also.
For SQL Server, you need to get this update at a minimum. You must also be on the SP4 update before you do this.

I strongly recommend you move to newer versions of Windows and SQL Server. Both versions that you have are heavily out-of-date and not receiving the latest security fixes, some of which are very serious.

Answered By - Charlieface

Answer Checked By - Robin (WPSolving Admin)

Sunday, July 24, 2022

[SOLVED] A connection was successfully established with the server, but then an error occurred during the pre-login handshake TCP Provider, error: 35

Issue

Solution