0

We are using SQL Server 2016 and want to use the Force Encryption option. From what I read, is it just installing the certificate on the Server itself and then setting the ForceEncyption to "Yes"? Is there anything needed on the client side like updating the connection string that it uses to connect? Or does setting the ForceEncryption take care of it?

Sorry, I'm new to encryption and we want to implement that on our SQL servers and it seems straight forward but I'm getting conflicting instructions as to whether the data connection string needs to be modified or not. We are also using AAG as well so will implementation be different as well?

Improve this question
4
  • Did you try the documentation?
    – LowlyDBA - John M
    Apr 11 '18 at 22:14
  • Yes, i have read the documentation but it still isn't clear on some points. I have read somewhere that once we set it on the server, it will be encrypted so we don't need to install the cert on all client servers. Just trying to see what is needed, do i need to change the connection string for an app to connect? OR does it encrypt it regardless?
    – kaitlyn
    Apr 11 '18 at 23:20
  • See my answer below, if you have ForceEncryption selected then that should ensure the server does NOT accept any connections unless they are encrypted. You can test this by trying to connect to the SQL Server instance in an insecure manner I suppose and see how it fails, and then connect with a client that does have the encryption method supported and confirm it works.
    – IT Thug Ninja
    Apr 12 '18 at 0:11
  • @PimpJuiceIT is correct. If you're confused because something is conflicting with official documentation, then try it out. What Pimp posted is a cut and paste from the documentation, so I'm not sure what you're expecting as an answer except just that.
    – LowlyDBA - John M
    Apr 12 '18 at 13:29
1

According to this StackOverflow post, if you have the server-level settings (referenced below) configured with ForceEncryption set to Yes, then that will enforce an encrypted connection regardless of the SSMS GUI Encrypt connection option being checked or not prior to making connections to that server. This may be a sufficient workaround for people where the "encrypted connection" is of more importance than the actual option being checked within the SSMS GUI.

According to Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager), you do this as such:

To configure the server to accept encrypted connections

  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties.
  2. In the Protocols for Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
  3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
  4. Restart the SQL Server service.

To configure the client to request encrypted connections

  1. Copy either the original certificate or the exported certificate file to the client computer.
  2. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.
  3. In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.
  4. On the Flags page, in the Force protocol encryption box, click Yes.

source

Improve this answer
-1

The server certificate doesn’t get exported to the client computer but the client does need to have its own certificate. Both certificates need to be descended from a recognized and trusted root authority.

If the SQL Server service is running under an account that’s not a local administrator, you’ll need to grant Read permission on the certificate to the service account.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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