How to Solve the Pre-Login Handshake Error in SQL Server

If you are using SQL Server with .NET Core, you may encounter the following error when trying to connect to your database:

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)

1	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)

This error means that there is a problem with the SSL/TLS encryption between your client and your server. SSL/TLS is a protocol that secures the communication between your client and your server by encrypting the data and verifying the identity of the parties.

There are several possible causes and solutions for this error, depending on your environment and configuration. In this blog post, we will explain some of the common scenarios and how to fix them.

Scenario 1: You are using an outdated version of SQL Server or .NET Core

One of the possible causes of this error is that you are using an outdated version of SQL Server or .NET Core that does not support TLS 1.2, which is the minimum required TLS protocol for secure communication.

TLS 1.2 is supported by SQL Server 2012 or later, and by .NET Core 2.0 or later. If you are using an older version of SQL Server or .NET Core, you may need to upgrade to a newer version that supports TLS 1.2.

Alternatively, you can try to enable TLS 1.0 or TLS 1.1 on your server or client, but this is not recommended as these protocols are less secure and may be deprecated in the future.

To enable TLS 1.0 or TLS 1.1 on your server, you can use the following registry keys:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]

"Enabled"=dword:00000001

"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]

"Enabled"=dword:00000001

"DisabledByDefault"=dword:00000000

To enable TLS 1.0 or TLS 1.1 on your client, you can use the following code:

System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls | System.Net.SecurityProtocolType.Tls11;

1	System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls \| System.Net.SecurityProtocolType.Tls11;

Scenario 2: You are using a self-signed certificate or an untrusted certificate authority

Another possible cause of this error is that you are using a self-signed certificate or a certificate issued by an untrusted certificate authority (CA) on your server. A self-signed certificate is a certificate that is not issued by a CA, but by the server itself. An untrusted CA is a CA that is not recognized by your client.

By default, .NET Core does not trust self-signed certificates or untrusted CAs, and will reject the connection if it cannot verify the identity of the server.

To fix this error, you have two options:

You can obtain a valid certificate from a trusted CA and install it on your server. This is the recommended option as it provides the highest level of security and trust for your communication.
You can choose to trust the self-signed certificate or the untrusted CA by setting TrustServerCertificate=true in your connection string. This option is less secure and should be used only for testing or development purposes.

For example, if you want to trust the server certificate, you can use the following connection string:

var builder = new SqlConnectionStringBuilder
{
    DataSource = "server",
    UserID = "user",
    Password = "password",
    InitialCatalog = "database",
    TrustServerCertificate = true
};
var connection = new SqlConnection(builder.ToString());

var builder = new SqlConnectionStringBuilder

{

DataSource = "server",

UserID = "user",

Password = "password",

InitialCatalog = "database",

TrustServerCertificate = true

};

var connection = new SqlConnection(builder.ToString());

Scenario 3: You are using a Linux or macOS client with .NET Core

Another possible cause of this error is that you are using a Linux or macOS client with .NET Core to connect to SQL Server. .NET Core uses OpenSSL as the underlying library for SSL/TLS encryption on Linux and macOS platforms. However, OpenSSL may have some compatibility issues with SQL Server’s implementation of SSL/TLS.

One of these issues is related to the cipher suites that are used for encryption. A cipher suite is a combination of encryption algorithms that are used to secure the communication between the client and the server. SQL Server requires a specific set of cipher suites that may not be supported by OpenSSL’s default configuration.

To fix this error, you need to change OpenSSL’s default configuration to allow more cipher suites that are compatible with SQL Server. You can do this by editing the /etc/ssl/openssl.cnf file and changing the following line:

CipherString = DEFAULT@SECLEVEL=2

1	CipherString = DEFAULT@SECLEVEL=2

CipherString = DEFAULT@SECLEVEL=1

1	CipherString = DEFAULT@SECLEVEL=1

This will lower the security level of OpenSSL and enable more cipher suites that are supported by SQL Server.

Alternatively, you can use a Windows client with .NET Core to connect to SQL Server, as Windows uses a different library for SSL/TLS encryption that does not have this issue.

Conclusion

In this blog post, we have explained how to solve the pre-login handshake error in SQL Server when using .NET Core. We have covered some of the common scenarios and solutions for this error, such as upgrading your SQL Server or .NET Core version, trusting the server certificate, or changing OpenSSL’s configuration. We hope this post has been helpful and informative for you. If you have any questions or feedback, please leave a comment below.