Sql-server – SQL Server 2016 Force Encryption Option

Securitysql server

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?

Best Answer

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

Related Question