8

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?

1
  • So is there a way to force encryption from server and reject all insecure connections irrespective of client's configuration or all the connections from client with invalid SSL? What's the point of ForcingEncryption on server if client side configuration can manipulate it ?
    – Jay Shah
    Jun 1, 2020 at 18:56

2 Answers 2

8

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

1
  • So how do we force encryption from server and reject all insecure connections irrespective of client's configuration or all the connections from client with invalid SSL? What's the point of ForcingEncryption on server if client side configuration can manipulate it ?
    – Jay Shah
    Jun 1, 2020 at 18:58
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).

1
  • I configured ForceEncryption to yes and selected the certificate on SQL server. It's still allowing the connections from clients that are not passing the valid certificate and clients that are not using encryption. How do I configure server to reject those clients ?
    – Jay Shah
    Jun 1, 2020 at 19:01

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.