Anyway to hook into the certificate chain validation / verification?

[drdamour]

at my corp all the SQL server certs are signed chained to a self issued corp ca, and it's not chained to any public CAs in anyway. This is easy enough when running in a vm to add the ca to the trust store and things seem to work.

but when i run a .net app in azure app service (non dedicated) or function app (like consumption) i seem to be out of luck as i'm not seeing a way to hook into/override certificate verification to see if the cert has a valid path to my corp ca.

Is this something that's supported by this library, or some lib this library depends on? something similar to what i can do with HttpClient and https://learn.microsoft.com/en-us/dotnet/api/system.net.servicepointmanager.servercertificatevalidationcallback?view=net-7.0

[JRahnama]

We have tested with self-signed certificates which do not have any CA attached to them and there has been no issues in that matter. We will do some research and will get back to you.

[drdamour]

that would work to, as long as you are NOT saying you are using TrustServerCertificate=true

[drdamour]

@JRahnama any follow up available here?

[JRahnama]

@drdamour is it possible for you test with this nuget locally and see the error message? Just change the extension to nupkg and add it to a folder and add that folder to your nuget.config file as a local provider.

For some reason GitHub does not upload the file. It locks at upload task.

[drdamour]

@JRahnama what am i testing? i'm looking for a way to specify my root certs to trust is that doen thru code with this package or config or the connection string or?

[JRahnama]

We have improved the error messages in the driver, but it has not been released yet. Wanted to know what issue is being detected with the certificate.

[shueybubbles]

@JRahnama
I have a different version of the same question (maybe?).
If an application tries to connect to a server with Encrypt=true and Trust Server Certificate=false but the connection fails due to cert validation failure, how can the application show the certificate details to the user to ask them whether they want to try again with Trust Server Certificate=true ? AFAICT the Win32Exception just has a message why the validation failed but not any data about the cert itself.

[David-Engel]

It can't. We'd need to add some way for SqlClient to return that to the application. I can see this also being useful for the ServerCertificate option as it would make it easier to get the certificate in the first place. From our previous conversation, I'm wondering if the ServerCertificate option would be better than TSC for what you are trying to do. I'm not sure you could even get the cert on TDS 7 connections, though, without SqlClient's help.

[shueybubbles]

yes I think I will open a feature request issue for this.
For SSMS we want to show the cert to the user so they can decide whether TSC is appropriate, and we want to display it as part of the connection properties like a web browser does.
If we can automate installing it locally, even better.
Getting the certificate out-of-band from the driver has other risks based on how the network stacks work with proxies etc.

[Recurse-blip]

This is a feature that would greatly benefit our product.

Currently, we default to using TrustServerCertificate=True, but we recognize this as a poor security practice. We aim to change the default to TrustServerCertificate=False.

Rather than having the TLS connection fail outright, we would like to enhance the user experience by presenting a dialog similar to an RDP connection. This dialog would display the certificate information and offer options to Continue, Continue & Remember, or Abort.

To achieve this, the library could potentially use the existing .NET callback:
ServicePointManager.ServerCertificateValidationCallback

Alternatively, it could be a custom callback function in the SqlConnection.

Regards,
Bronislav

[shueybubbles]

See #2353