8

I have a SQL server version 12.0.6205 and the following code in a .Net Core 3.1 (or 6) Web Api controller:

 var builder = new SqlConnectionStringBuilder
{
    DataSource = DataSource,
    UserID = UserID,
    Password = Password,
    InitialCatalog = InitialCatalog,
    ApplicationIntent = ApplicationIntent.ReadWrite,
    
    // Same result if true/true
    Encrypt = false,
    TrustServerCertificate = false
};

var connection = new SqlConnection(builder.ToString());
using (var cmd = new SqlCommand() { Connection = connection, CommandText = "SELECT TOP 1 * FROM [dbo].[Table]" })
{
    connection.Open(); // Breaks here
    var reader = cmd.ExecuteReader();
    Console.WriteLine(reader.HasRows);
}

Locally this code works without issues but when executing in Azure's App service it breaks when opening the connection 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: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed)

I also have been able to reproduce the error, locally, if creating a docker imaged based on appsvc/dotnetcore (any tag)

FROM mcr.microsoft.com/appsvc/dotnetcore:3.1-latest_20220105.1

ENV ASPNETCORE_URLS=http://+:80  

EXPOSE 8080
WORKDIR /home/site/wwwroot/
COPY . .

ENTRYPOINT ["dotnet", "Test.dll"]
UTurista
  • 176
  • 1
  • 1
  • 6

3 Answers3

5

Like @DraggonDragger said this is a TLS issue but in my specific case I can't rely on the SQL server being updated to allow TLS 1.2 therefore I had to allow TLS 1.0 in the application.

I ended up following this answer: https://stackoverflow.com/a/61523341/17892120;

Essentially, for docker images, adding this line is enough: RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

And for Azure's App Service, we can add sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf && dotnet Test.dll for the startup command.

For future reference, there's also a discussion about this in GitHub, link.

UTurista
  • 176
  • 1
  • 1
  • 6
  • could you help look: https://github.com/microsoft/azuredatastudio/issues/11249#issuecomment-1455376523 – Shawn Mar 06 '23 at 03:18
  • Thanks. This worked for me. I'm adding a TODO for my situation to migrate the database to a more modern windows server and sql server and then remove this openssl legacy workaround once that's done. I'm concerned that Encrypt=false had no effect (even though it's not best practice to not encrypt this kind of traffic) since it is misleading, it's still trying to encrypt and fails miserably. – Arizon May 03 '23 at 11:32
1

Found a similar issue in GitHub which is TLS HandShake Failure in SQL Server Pre-Login Handshake with Docker Image.

Also, This error typically occurs in client environments like docker image containers, Unix clients, or Windows clients where TLS 1.2 is the minimum supported TLS protocol.

Install the latest updates on supported versions of SQL Server1 and ensure the TLS 1.2 protocol is enabled on the server.

  1. Install the target SQL Server's TLS/SSL certificate in the client environment. It will be validated if encryption is needed.
  2. Set the "Trust Server Certificate = true" property in the connection string.
  3. Generate a new TLS/SSL Certificate for the server whose hash is signed with at-least the SHA-256 hashing algorithm.

Reference: Pre-Login Handshake Error Solution from Microsoft

DraggonDragger
  • 82
  • 3
1
RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

to your Dockerfile right above the ENTRYPOINT

https://github.com/dotnet/SqlClient/issues/633

desertnaut
  • 57,590
  • 26
  • 140
  • 166
Ashraf Alngar
  • 21
  • 1