A connection was successfully established... (provider: SSL Provider, error: 31 - Encryption( ssl /tls) handshake failed)

Question

I do have a docker container for a proxy (nginx), user interface and API (.NET 6). The API is giving me an error of 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).

Deployment Environment: CentOS 7

SQL Server Version: Microsoft SQL 2016 SP2

Connection String: Data Source=${DB_HOST};Initial Catalog=${DB_NAME};Persist Security Info=True;User ID=${DB_USERNAME};Password=${DB_PASSWORD};MultipleActiveResultSets=True

Solutions I'd tried:

1. Enabling TLS 1.2 in regedit in Database server.
2. Restarted SQL server agent.
3. Added TrustServerCertificate=True or Encrypt=False in the connection string.
4. Added RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf

Nothing worked from all of this solutions. Badly needed help!

Answer 1

I just solved my concern by combining the following.

• Used Microsoft.Data.SqlClient instead of Systems.Data.SqlClient.
• Added commands to Dockerfile of the API

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

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

• Added TrustServerCertificate=True to connection string of the database.

This lowers the TLS version of docker image or container to TLSv1. However, this is not the best solution. It would be better to upgrade the SQL server to allow the connection to accept TLSv1.2. Which in my case is not applicable.