6

As I am able to configure my SQL Server and client to establish SSL connection, I have installed the certificate on the server as well as on the client machine (Trusted Root Certification Authorities store).

If I have only one certificate on server and client, it is working fine.

Now my question.

If I have multiple certificates on both machines then I need to attach the certificate to the SQL server which I want to use, but I am not telling my client which certificate to use. Then how does the client pick the correct certificate from its store for handshaking with SQL Server?

Does it loop through all the certificates from its store and get the one matching with the SQL Server's certificate?

How does the client identify the correct certificate (generally it exchanges the public keys from the certificate) from its store?

Is there any way to configure the client to use XXXX certificate for the SQL Server 1 and YYYY certificate for the SQL Server 2?

Improve this question
1
  • 1
    here is a classic explanation explanation of the verification process. I've linked you partway down the page where certificate verification starts, but the whole discussion of establishing an HTTPS session is really interesting. moserware.com/2009/06/…
    – Owen
    Commented Jul 19, 2016 at 3:27

1 Answer 1

Reset to default
4

The sever sends its certificate during the TLS handshake and the client verifies its trust path.

In the first step the client matches the common name (CN) field with the server's hostname (it does not compare the certificates).

Then the client verifies the trust path, i.e. whether the certificate presented by the server is signed by an entity whose certificate is in the trusted root certificates store (again: it does not match the certificate directly)

  • if you use a self-signed certificate and add it to the store, then the verification is successful when server's certificate is in the store itself;
  • if you use a (self-signed) CA, then the verification is successful, when signing CA's certificate is in the client's trusted store.

Does it loop through all the certificates from its store and get the one matching with the SQL Server's certificate?

Remember the client is not trying to match the two certificates (sent by server and stored on the client), but to verify the certificate used to sign the sent certificate.

As for the process itself, you can think of it this way, but whether it checks the trust path by looping through all entries or uses an index to speed up the process, is an implementation detail that does not really matter to the user.

Is there any way to configure the client to use XXXX certificate for the SQL Server 1 and YYYY certificate for the SQL Server 2?

The client will verify the trust of the certificate sent by the server during the handshake phase. So effectively they will be different for SQL Server 1 and SQL Server 2 and it will work the way you described without any additional configuration.

As a sidenote, you shouldn't be really managing by adding a self-signed certificate for each server separately, but create a CA and sign sever certificates using it. Then you distribute only one CA's certificate to all clients.

Improve this answer
1
  • "In the first step the client matches the common name (CN) field with the server's hostname(...)" a certificate have two places where the CN is stored. The issuer and the subject. What CN is compared? The CN of the issuer or the subject?
    – Grim
    Commented Jan 9, 2021 at 10:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .