Running .NET Core unit tests with SQL Server in a Docker container: sqlException: handshake Problem

[M-Patrone]

How can I run unit tests in a .NET Core Test project with a SQL Server running in a Docker container? I have set up a SQL Server in my Dockerfile and can successfully run the unit tests if I manually build and start the Docker container, navigate to the "Test" folder in the container and run the tests with "dotnet test".

However, if I try to run the tests by starting the container with a command, I always get an error message.

Microsoft.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: 0 - Success)  

The connection string looks like this:

SqlConnection sqldr = new Microsoft.Data.SqlClient.SqlConnection("Server=host.docker.internal;User ID=sa;Password=abcDEF123#;Encrypt=false;MultiSubnetFailover=true;TrustServerCertificate=true");

And here is the Dockerfile:

FROM mcr.microsoft.com/mssql/server:2019-latest as sqlserver

USER root

ENV ACCEPT_EULA=Y 
ENV SA_PASSWORD=abcDEF123#
ENV MSSQL_PID=Developer
ENV MSSQL_TCP_PORT=1433 

#copy all the sql files to the docker container
#COPY /Database/Scripts /database/ 
RUN ((/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started") 

COPY . .

# install the dotnet environment
RUN apt-get update && \
    apt-get install -y dotnet-sdk-6.0 

That is the command how I build the container:

docker run -d sqlserver_sqltoolsservice

That is the command which generates the error:

docker run -p 1433:1433 sqlserver_sqltoolsservice /bin/bash -c "cd Data.Test && dotnet test"

I also tried to add the following to the dockerfile:

RUN sed -i 's/DEFAULT@SECLEVEL=2/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/g' /etc/ssl/openssl.cnf
RUN sed -i 's/DEFAULT@SECLEVEL=2/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/g' /usr/lib/ssl/openssl.cnf

RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/CipherString = DEFAULT@SECLEVEL=2/CipherString = DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

[JRahnama]

@M-Patrone thanks for opening the issue. We will look into this and will get back to you.

[M-Patrone]

@M-Patrone thanks for opening the issue. We will look into this and will get back to you.

@JRahnama I apologize for the inconvenience, but I wanted to inquire if there has been any update regarding the matter. I would greatly appreciate any information you can provide. Thank you for your understanding,

[perlun]

@M-Patrone - did you ever manage to find a solution to this?

For the record, also seeing something similar here when running test with TestContainers.net (https://dotnet.testcontainers.org/) towards an mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04 SQL Server instance. :thinking: Tested first with System.Data.SqlClient where I got the same error, switched over to Microsoft.Data.SqlClient to no avail.

Here is the full exception @JRahnama and others.

Microsoft.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: 0 - Success)
Data:
  HelpLink.ProdName: Microsoft SQL Server
  HelpLink.EvtSrc: MSSQLServer
  HelpLink.EvtID: 0
  HelpLink.BaseHelpUrl: https://go.microsoft.com/fwlink
  HelpLink.LinkId: 20476
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, 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, Func`3 accessTokenCallback)
   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 ---

My code is rather simple. An NUnit test, with a [OneTimeSetup] method that looks roughly like this. Any ideas?

[OneTimeSetUp]
public async Task OneTimeSetup() {
    _msSqlContainer = new MsSqlBuilder()
        .WithStartupCallback(async (container, cancellationToken) => {
            string connectionString = container.GetConnectionString();

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

            using var command = connection.CreateCommand();
            command.CommandText = "boom";

            await command.ExecuteNonQueryAsync(cancellationToken);
        })
        .Build();

    await _msSqlContainer.StartAsync();
}

[perlun]

For the record, also seeing something similar here

This turned out to be a user error on my behalf. :see_no_evil: Please disregard, my problem is now resolved.