Sql-server – SQL encrypted connection home experiment

sql serversql-server-2012

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.

Best Answer

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.