MS SQL Server accepts non-SSL connections even with Force Encryption enabled on the server side

Question

I am forcing encryption on my SQL Server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

Here are my detailed steps :

• makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
• I imported the same certificate into the trusted Root Certification Authorities Store
• In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
• On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
• On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
• Restarted the SQL Server service.

Am I missing anything else?

Answer 1

Yes, this is correct. It's also described here Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) And maybe also this discussion helps: force-encryption-on-sql-server-not-working

Answer 2

There are two ways to encrypt the SSL connection.

1. Configure server for all the incoming client request for SSL.

2. Selective client configuration.

In the first case if you installed certificate on the server and make "Force encryption to yes" then no matter which client is querying for connection, it will always create SSL connection.

In the second case you need to have the same certificate installed on server and client (trust root certificate authorities store) machine.Then it will create SSL connection for that client otherwise it will fail to establish SSL Connection (provided you check the Encrypt connection in SSMS). If Encrypt connection is not checked then it will create connection but not secured (SSL).