In SQL Server and using only client side encryption requests, can I use a different certificate for each client?

Question

Based upon the UI dialogue box in SQL Server Configuration Manager, if I right click 'Protocols for server_name', it appears that I can only select one certificate to use.

My experience so far tells me that this certificate option is not for server-side 'force encryption', the flag option one tab over. This is because server-side force encryption does not require that the client have a copy of the public key/ certificate. Any client that wishes to connect can connect provided that they are authorized to connect and a temporary certificate will be used for the encryption. It is only when a client makes the request that a connection be encrypted (client-side encryption) does it matter whether the client has a copy of the certificate selected in the SQL Server Configuration Manager.

The UI appears to limit the number of certificates to one but perhaps this is simply poor UI. Is it possible, say by powershell commands, to establish a pool of certificates, with different durations, by which various kinds of clients could connect?

Answer 1

SQL Server SSL encryption that is initiated from the server does not require that the client have a copy of the certificate in advance. The certificate and private key are stored in the certificate store of the service account on the server. If the certificate is selected using SQL Server Configuration Manager and Force Encryption is set to true, then whenever a client requests a session, the server replies with the encryption requirement and sends a copy of the certificate, which contains the public key. To establish an SSL or TLS session, the client randomly selects a symmetric session key and transmits it back to the server encrypted by the public key. This random key is then used as the basis for secure communication. It is recommended to use a domain certificate or CA certificate and not a self signed certificate as a self signed certificate would leave you vulnerable to a man in the middle attack.

There is also a way to request from the SQL Server Management Studio client that a session be encrypted. I don't see any certificate configuration, so it may use a self signed certificate generated at the client to initiate a secure connection. Either way, you can see if any connections are encrypted using the sql statement:

    select encrypt_option, * from sys.dm_exec_connections