web api in Docker can't connect to SQL Server on host with pre-login handshake error

Question

Firstly there are a few similar questions but I have tried all the suggestions I can find and nothing seems to work. If you can find one I haven't mentioned, please comment and I'll try it out.

The synopsis is I'm trying to connect a .NET Core 3.1 web api in a Docker container to SQL Server 2019 on my host, but it fails with 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: 0 - Success).

I have:

Docker (desktop) version 20.10.7, build f0df350 using WSL2
SQL Server Developer 2019 15.0.2080.9
Windows 10 Home 21H1

I have tried:

A different docker image

According to here people have success with mcr.microsoft.com/dotnet/core/aspnet:3.1-bionic instead of mcr.microsoft.com/dotnet/core/aspnet:3.1

port mapping

various forms to docker run, including -p 1433:1433, -p 0.0.0.0:1433:1433, -p localhost:1433:1433

Connection Strings

My connection string is currently "Server=host.docker.internal;Database=xxx;MultipleActiveResultSets=true;UID=sa;PWD='xxx';Integrated Security=false;TrustServerCertificate=true"

I have also tried Server=host.docker.internal,1433, Server='tcp:host.docker.internal\SQLSERVER2019,1433', Server='tcp:host.docker.internal,1433', Server='host.docker.internal,1433'...

SQL Server configuration

TCP is enabled and listening on all interfaces, however I can't see an IP address in the range 192.168.65.0/24 which is what Docker desktop is configured to use.

SSMS works locally, and the web api app connects if it's not in a Docker container.

TLS 1.2

Some comments say it's due to a TLS error with a newer version of SQL Server, however I've tried the workarounds:

TrustServerCertificate=true
changing the minimum protocol on the container:

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

Dockerfile

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-bionic AS base
WORKDIR /app
EXPOSE 80

ENV ASPNETCORE_URLS=http://+:80

FROM mcr.microsoft.com/dotnet/sdk:3.1 AS build
WORKDIR /
COPY . .
RUN dotnet build my.csproj -c Release -o /app/build

FROM build AS publish
RUN dotnet publish my.csproj -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /etc/ssl/openssl.cnf
RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /usr/lib/ssl/openssl.cnf
RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /usr/lib/ssl/openssl.cnf
ENTRYPOINT ["dotnet", "my.dll"]

Full Stack trace

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: 0 - Success)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:88dafbd1-163f-486b-a750-ec3dbf5c75ff

Thanks!

[edit]

When I connect to localhost using SSMS, I get a different SQL Server instance. So I tried to connect to the correct named instance from the connection string using Server=host.docker.internal\SQLSERVER2019; I get the error: System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 25 - Connection string is not valid)

If I try and use Server=host.docker.internal\SQLSERVER2019; I get Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 25 - Connection string is not valid)

Note: I'm setting the connection string as an environment variable to docker run: -e ConnectionStrings__MyConnectionString="..."

user_1089267 · Answer

The SQL Server named instance I wanted to connect to was not using port 1433!

When you use SSMS to connect to . amedinstance it uses the correct (dynamic) port for that instance, so this was throwing me off. Using SSMS to connect to localhost gave me an incorrect password error, so I suspected I was connecting to the wrong instance.

I used SQL Server Configuration Manager to adjust the ports for the instance I wanted:

navigate to the TCP/IP settings for the instance you don't want to connect to
in my case this instance was bound to 1433. For all TCP Dynamic Ports enter "0" and set all TCP Port to empty (make sure you do all IPxx items in the list)
restart the instance that you just edited
repeat steps 2-3 for the named instance that you want to set to 1433, except this time, set all TCP Dynamic Ports to 0 and set all TCP Port to 1433
restart docker

Oddly, after doing this I couldn't use the syntax -p 127.0.0.1:1433:1433 as I would get an error docker: Error response from daemon: Ports are not available: listen tcp 127.0.0.1:1433: bind: An attempt was made to access a socket in a way forbidden by its access permissions. time="2021-08-25T10:12:15+09:30" level=error msg="error waiting for container: context canceled"

However if I used the simpler syntax: -p 1433:1433 then docker was happy (as was I!)

user_945687 · Answer

I ran into this issue today, and I had three instances of SQL Server running on the same machine. 2012, 2014, and 2016. I wanted to connect to 2012, as thats where the database is. I tried setting the port as mentioned in @thinkOfaNumber's answer but this did not work for me.

What worked was setting the TLSv1.2 in the docker file;

FROM mcr.microsoft.com/dotnet/aspnet:3.1 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443
RUN sed -i 's/TLSv1.2/TLSv1.0/g' /etc/ssl/openssl.cnf