1

I have a home SQLserver 2012 that I managed to enable "force encryption" on, using a self-signed certificate and configuration manager. Now I find two things I can't explain:

  1. Once "Force Encryption" is enabled, I expect other computers on my home network to NOT be able to connect unless the certificate has been transferred and loaded on the client machine. That worked for one client computer, but a second client computer seems to be able to connect WITHOUT the certificate being imported (using mmc, certlm.msc or certmgr.msc).

  2. When I "clear" the certificate in Configuration Manager, I can still use encrypted connections. It's only when I set "force encryption" to No that connections are NOT encrypted. But then, when I set it to Yes and restart the SQLserver (without certificate selected in Protocol Properties), I can again use encrypted connections, according to: SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

Question 1 is: why can I use encrypted connections although the certificate has been Cleared in Protocol Properties?

Question 2 is: why can the second client computer connect to the SQLserver although Force Encryption is enabled, and, more importantly, what is preventing any other computer to connect, why use encryption at all?

SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (Intel X86). NO Active Directory, NOT explicitly configured in any DNS, just my ISP router.

Improve this question

1 Answer 1

Reset to default
0

Maybe you have set up 'trust server certificate' option in the client SSMS. With this option the client accept the certificate of the server as is, without verofying it with the private key stored in the certificate repository.

Improve this answer
4
  • No I'm using SQLCMD with just the S, U and P parameters.
    – SamBalby
    Mar 13, 2021 at 18:24
  • But you seem to be thinking in the right direction: on yet another system I installed a fresh SQL2019, and found that it is possible to just set "Force Encryption" to Yes and actually use encrypted connections. Without installing any certificate whatsoever. Which would make the question: what makes the SQLserver system "trusted".
    – SamBalby
    Mar 13, 2021 at 20:44
  • There are two things that makes sql server trusted. 1) there is a certificate installed on the client machine that allows to verify the public part of the sql certificate. 2) the client as the 'trust server certificate' option enable so it does not verify the certificate and trust it. Another important thing is that the certificate are bound on network names (dns names) not on IP addresses. In fact you have to use FQDN in a real envirnoment.
    – MBuschi
    Mar 14, 2021 at 12:49
  • 1
    1) I have NOT installed a certificate on the new 2019 SQLserver (I assume you are talking about any certificate set in Configuration Manager , Network properties) and 2) I have NOT specified the "trust server certificate" option on the client. Nevertheless, I DO get an encrypted connection. Why?
    – SamBalby
    Mar 22, 2021 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.