Encrypt=false not working

[vasicvuk]

Describe the bug

I am trying to connect to SQL Server using Encrypt=False but I cannot get it to work. SQL Server is 2016 placed on Windows Server and configured to not require encryption.

Connection string used:

Data Source=hostname\\aaa;Initial Catalog=SampleDB;User ID=sa;Password=Sample123;Pooling=True;Min Pool Size=3;Max Pool Size=10;Connect Timeout=5;Trust Server Certificate=False;Encrypt=false;

Exception message:
Unhandled exception. 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.

Further technical details

Microsoft.Data.SqlClient version: 4.1.0
.NET target: 6.0
SQL Server version: (e.g. SQL Server 2016)
Operating system: Alpine Linux .NET 6 Runtime container

Additional context
This does not happen when the client is a Windows machine.
.NET 3.1 based service works without any issue

[DavoudEshtehari]

Hi @vasicvuk,

The pre-login process attempts to encrypt just the login if there were any supported encryption in between even though Encrypt=false.

Could you elaborate on a sample console app with steps to reproduce the issue? It's not clear where the SQL Server is located!

Also, could you verify your application by adding the following line on application startup?
AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.EnableSecureProtocolsByOS", true);

[vasicvuk]

Hi @DavoudEshtehari,

Here is the source code of a sample console app that I tried:

using Microsoft.Data.SqlClient;
AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.EnableSecureProtocolsByOS", true);
var connectionString = "Data Source=hostname\\aaa;Initial Catalog=SampleDB;User ID=sa;Password=Sample123;Pooling=True;Min Pool Size=3;Max Pool Size=10;Connect Timeout=5;Trust Server Certificate=False;Encrypt=false;";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

Unfortunately, I get the same exception.

Also here is a Docker file I am trying with:

FROM mcr.microsoft.com/dotnet/sdk:6.0 AS build
WORKDIR /src
COPY ./* /src/
RUN rm -rf bin obj Debug global.json
RUN dotnet publish -c Release -o out -f net6.0

FROM mcr.microsoft.com/dotnet/runtime:6.0-alpine
COPY --from=build /src/out . 
ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=false
RUN apk add icu
ENTRYPOINT ["dotnet", "TestSQLServer.dll"]

As I said before SQL Server 2016 is located on Windows Server-based Virtual machine. Connection works with .NET Core 3.1 and Microsoft.Data.SqlClient version 2.1.3 without any issue also on Alpine-based .NET Core Runtime image.

Changing openssl.conf on Alpine image so that CipherString Is set to DEFAULT:@SECLEVEL=1 fixes the issue but I would like option to just disable Encryption also in the Login phase if this is something that was supported by older libraries

[Wraith2]

I would like option to just disable Encryption also in the Login

I assumed this wasn't possible but I thought I should check and see why. So i looked at the spec and interestingly it says:

The TDS server receives the first packet from the client. The packet SHOULD be a PRELOGIN packet to set up context for login. A Pre-Login message is indicated by the PRELOGIN (0x12) message type described in section 2. The TDS server SHOULD close the underlying transport connection, indicate an error to the upper layer, and enter the "Final State" state, if the first packet is not a structurally correct PRELOGIN packet or if the PRELOGIN packet does not contain the client version as the first option token. Otherwise, the TDS server MUST do one of the following:
§ Return to the client a PRELOGIN structure wrapped in a table response (0x04) packet and enter "TLS/SSL Negotiation" state if encryption is negotiated.
§ Return to the client a PRELOGIN structure wrapped in a table response (0x04) packet and enter unencrypted "Login Ready" state if encryption is not negotiated.

So in theory if the server allows no encryption and the client allows no encryption then it is possible to skip the login encryption. However looking at the code for this library I found that:

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs

Lines 900 to 905 in 38dfaaa

	case (EncryptionOptions.OFF):
	if (serverOption == EncryptionOptions.OFF)
	{
	// Only encrypt login.
	_encryptionOption = EncryptionOptions.LOGIN;
	}

So even if the client requests no encryption and the server would allow it we force login to be encrypted to protect the credentials being used. I think this is a good idea and the uses cases for totally disabling encryption aren't good. Is there compelling reason that you can't simply change the container security?

[vasicvuk]

Changing it to level 1 on openssl will also affect other components allowing SSL 3.0 and lower bits of encryption to be used and we don't want to do that. In general i don't see a point of using encryption if I must enable this security level on openssl as it will just create another security problem.

https://www.openssl.org/docs/man1.1.1/man3/SSL_CTX_set_security_level.html

Level 1
The security level corresponds to a minimum of 80 bits of security. Any parameters offering below 80 bits of security are excluded. As a result RSA, DSA and DH keys shorter than 1024 bits and ECC keys shorter than 160 bits are prohibited. All export cipher suites are prohibited since they all offer less than 80 bits of security. SSL version 2 is prohibited. Any cipher suite using MD5 for the MAC is also prohibited.

Maybe some feature flag disabling encryption on login stage would be useful

[Wraith2]

The first thing to do is fine out if it's possible to configure the server to allow encryption-less logins because if it isn't then there's no point starting to try and change this library. If that is possible then it'll be up to the MS team to make a policy decision on whether it's worth allowing an unsafe use-case.

[vasicvuk]

If this was never allowed then there is another problem with client since the login passed without any issue on .Net 3.1 and library version 2.1.3 without specifing Encrypt=False in connection string

[Wraith2]

Are you sure that the 3.1 base image was using the same version and configuration of alpine? because I thought this issue was mainly caused by slimlining of the underlying os image. The code around the login process hasn't changed much.

[strainovic]

Hi @Wraith2,

I've found this document https://docs.microsoft.com/en-us/dotnet/core/compatibility/cryptography/5.0/default-cipher-suites-for-tls-on-linux

It is related to .Net 5, but I'm sure it applies to .Net 6 also.

I suppose it worked because, for .Net 5, Microsoft didn't respect the OpenSSL default configuration from the container?

Thanks

[DavoudEshtehari]

The implementation is based on MS TDS protocol:

Depending upon whether the server has encryption available and enabled, the server responds with an ENCRYPTION value in the response according to the following table.

Value sent by client	Value returned by server when server is set to ENCRYPT_OFF	Value returned by server when server is set to ENCRYPT_ON	Value returned by server when server is set to ENCRYPT_NOT_SUP
ENCRYPT_OFF	ENCRYPT_OFF	ENCRYPT_REQ	ENCRYPT_NOT_SUP
ENCRYPT_ON	ENCRYPT_ON	ENCRYPT_ON	ENCRYPT_NOT_SUP (connection terminated)
ENCRYPT_NOT_SUP	ENCRYPT_NOT_SUP	ENCRYPT_REQ (connection terminated)	ENCRYPT_NOT_SUP

Assuming that the client is capable of encryption, the server requires the client to behave in the following manner.

Client	Value returned from server is ENCRYPT_OFF	Value returned from server is ENCRYPT_ON	Value returned from server is ENCRYPT_REQ	Value returned from server is ENCRYPT_NOT_SUP
ENCRYPT_OFF	Encrypt login packet only	Encrypt entire connection	Encrypt entire connection	No encryption
ENCRYPT_ON	Error (connection terminated)	Encrypt entire connection	Encrypt entire connection	Error (connection terminated)

Note: The ENCRYPT_NOT_SUP value by client doesn't support using Managed SNI on Linux.

[vasicvuk]

@DavoudEshtehari This I understand, but maybe issue is in using OpenSSL configuration from .NET 5 instead of using separate configuration as @strainovic said. The question is can we relax this configuration only for MSSQL client which will not affect other components on system.

[angularsen]

In my case, I was running mssql/server:2019-later in Docker on an Azure Pipelines build agent.

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

Encrypt=False in the connection string did not help.

From SQL logs, it turned out the problem was with the mounted volumes: ERROR: BootstrapSystemDataDirectories.
This is a known issue: microsoft/mssql-docker#602 (comment)

By not mounting Docker volumes, I could connect. So the original error message threw me off in the wrong direction, debugging SSL and TLS1.2.

Maybe this helps some others googling this.

[vasicvuk]

We are not running Sql server in Container at all for this case. Its a Windows server machine

[JRahnama]

@vasicvuk can you try with the latest release of the driver (v5.0) and see if the issue still happens?

[JRahnama]

Closing due to inactivity. Feel free to comment here or open a new issue if the the issue is still happening.

[luber]

@JRahnama issue is still valid.

.NET SDK:
 Version:   7.0.100
 Commit:    e12b7af219

Runtime Environment:
 OS Name:     debian
 OS Version:  11
 OS Platform: Linux
 RID:         debian.11-x64
 Base Path:   /usr/share/dotnet/sdk/7.0.100/

Host:
  Version:      7.0.0
  Architecture: x64
  Commit:       d099f075e4

sample app:

TestApp.csproj:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net7.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>

    <ItemGroup>
      <PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.1" />
    </ItemGroup>

</Project>

Program.cs:

using Microsoft.Data.SqlClient;

Console.WriteLine("Connecting...");

string connectionString = "data source=SQL-SERVER-IP;user id=demo;pwd=demo;persist security info=False;Application Name=Test APP;initial catalog=TestDB;Encrypt=False";

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

Console.WriteLine("Connected!");

Results:

Connecting...
Unhandled exception. 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](CancellationToken cancellationToken)
   at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at Microsoft.Data.SqlClient.SNI.TdsParserStateObjectManaged.EnableSsl(UInt32& info, Boolean tlsFirst)
   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.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   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.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Program.<Main>$(String[] args) in /tmp/Program.cs:line 8
   at Program.<Main>(String[] args)
ClientConnectionId:b99b5e52-64cf-4a0a-b97d-2d4cb0858653

[ErikEJ]

What SQL Server version are you connecting to?

[luber]

@ErikEJ it's a bit old: 10.50.6560.

However, I can connect to it by running a sample app from my Mac.
But once it is deployed as a container (running on the k8s cluster node with Ubuntu 22.04.1 LTS) - I am getting those errors.

[ErikEJ]

@luber that Server version is no longer supported. Maybe it does not have TLS 1.2 enabled

[JRahnama]

@luber, just some hint from my own experience; while ago I had similar issues with Ubuntu v22.04 and net6/7 and that was related to openssl version in available net version in v22.04. The other related topic could be TLS1.3 which is enabled on 22.04 and other latest versions of Linux OS machines. TLS 1.3 is only works on SQL server 2022. There are some options to check the issue:

1. If you are able, can you test with some lower version of the container
2. Using WireShark you can check the used TLS version in server client communication to see what TLS version is being used.

[luber]

@JRahnama, thanks for the suggestions.
I have tried to use a lower version of .net runtime: 6.0 and 5.0 - produced the same results...

So, I have started looking at OpenSSL: the worker node (running Ubuntu 22.04) has OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022) installed, and the default configuration contains this:

[system_default_sect]
CipherString = DEFAULT:@SECLEVEL=2

And looks like MacOS does not use OpenSSL, but LibreSSL instead. I assume that is the reason it works on Mac.
Anyway, I will try to use Wireshark, as you suggested, to see how it goes...

[JRahnama]

@luber, what I was trying to say was the version of OS. For example, in my case I saw similar errors on ubuntu 22.04 and when I downgraded to Ubuntu v20.04 everything worked fine. The OpenSSL v3.0.* came with some breaking changes. This has been documented at net5 breaking changes. You can read more here.