4

I'm working on a Docker related application, written in C#, based on Entity Framework.

One of the Docker containers, is the ms-sql-server, which is used for database access.
In order to access the SQL-server, following connectionString is created:

Server=ms-sql-server;Initial Catalog=Ownobjects;User ID=SA;Password=some_password

While performing following source code:

public void InitialiseDatabase(IApplicationBuilder app)
{
  using (var context = app.ApplicationServices.GetRequiredService<ApplicationDbContext>())
  {
    context.Database.EnsureCreated();
    ...

... I get following Exception:

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: 35 - An internal exception was caught)'  

Inner Exception  
AuthenticationException: The remote certificate was rejected
  by the provided RemoteCertificateValidationCallback.

According to this similar StackOverflow post, two things might not match: the instance name in the connection string and the expected name from the instance.

  1. Is this true? In that case, where or how can I find both names?
  2. If this is not true, then what might be causing this issue?

Edit:
Some further examination:
In the Logs of the ms-sql-server Docker container, I've found following line:

Server name is 'e614890825ac'.
This is an informational message only.
No user action is required

I've used this entry in the connectionString but then the same Exception is raised, but this time with following Inner Exception::

ExtendedSocketException: Resource temporarily unavailable

Edit2:
Again some further examination:
In the meantime I've discovered that my original servername in the connectionString is correct.

Thanks in advance

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • Nobody has an idea? – Dominique Nov 28 '22 at 15:16
  • Use SQL Server Management Studio and in explorer check logs under Management. The SSMS login window should show same server\instance as in the connection string. The login window should show Window credentials and then the connection string should have Integrated Security = true (no username and password). – jdweng Nov 28 '22 at 15:55
  • @jdweng: Thanks for your quick reply, but I'm working in a Docker environment, which has some different rules than regular SQL-server. – Dominique Nov 28 '22 at 16:02
  • There is authentication and credentials. Still you need to check the SQL Logs to help isolate issues. If you do not see a failure in the logs than the issue is in docker. If you do see the error in the logs than error will indicate exactly what is wrong. – jdweng Nov 28 '22 at 16:09
  • 2
    Recent versions of `SqlClient` now default to having encryption on, rather than off. If this is the issue, and assuming you don't actually need an encrypted connection between your host and your container (which doesn't add much) adding `Encrypt=false` to your connection string should prevent issues with certificate checks (if you do explicitly want to have an encrypted connection, things get more complicated). – Jeroen Mostert Nov 28 '22 at 16:28
  • Heu... there are no logs: I mean the logfile is there, but when I launch my application, nothing has been added to the logs: neither the logs of Microsoft SQL-Server Management Studio, neither the ms-sql-server Docker container. – Dominique Nov 29 '22 at 07:27
  • have you solved this issue? could you share your Dockerfile (client) or the image that the client uses? – Pato Dec 07 '22 at 12:38

2 Answers2

8

Adding the following to the client's connection string solved it for me:

;TrustServerCertificate=true

GaTechThomas
  • 5,421
  • 5
  • 43
  • 69
1

I've spent some time solving this message SQL Server Pre-Login Handshake

Solution 1 (Encryption in client container):

Dockerfile client (github issue)

FROM mcr.microsoft.com/dotnet/aspnet:6.0 AS base
# ...

# SQL Server trusted connection problem
RUN sed -i 's/CipherString = DEFAULT@SECLEVEL=2/CipherString = DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

# ...

Note: There's no need to change your connection string adding Encrypt=false or anything else with this setting

Note 2: I'have tried to use focal and other linux images and only aspnet:6.0 works with this setting.

Solution 2 (Image version or storage problems):

  • Change the runtime version of the client container (see github comments)
  • Replace the binding storage or volume in sql server container. This could be a user access problem.

Note: There are some comments about this github issue

Pato
  • 462
  • 2
  • 4
  • 11