Remote certificate rejected when connecting to SQL Server from Linux host [closed]

Question

I have been provided a certificate for a SQL Server, which has been successfully installed and activated. I can confirm this from the logs:

The certificate [Cert Hash(sha1) "xxxxxxxxxxxxxxxxxxE5C050F7D75F58E4E2F"] was successfully loaded for encryption.

Connecting to the database using SSMS is successful, simply by encrypting the connection without trusing the server certificate.

I wanted to replicate this using WSL and later on - docker.

I am testing this with a simple .net 6 console application:

var con = new SqlConnection("Server=domain.host.eu,50730;Database=databasename;User Id=user;Password='password';");
await con.OpenAsync();
var version = con.ExecuteScalar<string>("SELECT @@VERSION");
Console.WriteLine(version);

This works, if I add Trust Server Certificate=True; to the connection string. Without it, the connection fails:

Unhandled exception. 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: 35 - An internal exception was caught)
 ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
   at System.Net.Security.SslStream.SendAuthResetSignal(ProtocolToken message, ExceptionDispatchInfo exception)
   at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
   at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](TIOAdapter adapter, Boolean receiveFirst, Byte[] reAuthenticationData, Boolean isApm)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)

I wanted to extract the certificate from the pfx:

openssl pkcs12 -in host.domain.eu.pem.pfx -clcerts -nokeys -out host.domain.eu.crt
sudo cp host.domain.eu.crt /usr/local/share/ca-certificates/
sudo update-ca-certificates

Unfortunately, this fails with the same error messages and I don't know where I went wrong. I can only assume, that my handling of the certs on Linux is wrong.

Answer 1

If you're connecting to something unimportant, I was experiencing this error connecting from my dotnetcore API, to a microsoft sql server contained in a docker container (all locally) for dev work. My solution was putting encrypt=False at the end of my connection string like so (within appsettings.json):

"DefaultConnection": "server=localhost;database=newcomparer;trusted_connection=false;User Id=sa;Password=reallyStrongPwd123;Persist Security Info=False;Encrypt=False"