Remote certificate rejected when connecting to SQL Server from Linux host

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"

Answer 2

I double blindsided myself and got distracted by 2 mishaps at the same time.

After importing a certificate into the SQL Configuration Manager, you can choose from 2 nigh on identical entries. The difference beeing one having a friendly name of host.domain (FQDN), while the other has no friendly name. Naturally I have not double checked and just stayed with the one without the FQDN as the friendly name, which was pre-selected after the import and looked valid. The second mistake was to not realize, that WSL was not part of our domain and could not resolve the database name. So I used to the FQDN to reach it. This caused the mismatch between host, which the database instance expected and host.domain, which the client from inside WSL used.

After this I borrowed an Ubuntu vm inside our domain and verified, that using only the host,port schema in the connection string works.

And to make it explicit and work inside wsl I switched the certificates around to require the FQDN as the friendly namae while connecting, so connections can be made from inside the domain, as well as "outside" (from wsl).

tl;dr: One possible explanation for the exception The remote certificate was rejected by the provided RemoteCertificateValidationCallback. is, that the instance name in the connection string and the expected name from the instance do not match.