“Force Encryption” vs “Force Protocol Encryption” in SQL Server

Posted on

Question :

I’m trying to understand how the different settings affect the behaviour of my server-client communication. On the server I have installed a self-signed CA with an issued exchange cert which also has been configured to be used by my SQL service.

At first I’d like to see this connection fail when the root CA has not been installed on the client. But whatever I do it seem to work anyway.

To better understand why, I’m trying to list all options and what effect they should produce. But I’m not sure I have understood it correct …

Can anyone please help me to correct and fill out the missing pieces here?

When enabling the “Force Encryption” setting on the SQL Server:

  • In practice this is the same as setting Encrypt=True;TrustServerCertificate=True; in my connection-string. The client have no say in whether encryption should be used or not and whether the server should be trusted or not.
  • This option can be used to encrypt individual service instances.
  • Supports self-signed exchange certs without CA.

When enabling the “Force Protocol Encryption” setting on the SQL Server:

  • All client connections to all services on the server are encrypted.
  • Requires an exchange cert issued by a trusted CA available on both client and server.

When using the “Force Protocol Encryption” setting on the Client:

  • This single client will force use of SSL and requires an exchange cert issued by a trusted CA available on this machine. Without it this connection will fail.

When enabling the “Force Protocol Encryption” setting on both Server and Client:

  • This is not recommended. But why? What happens and what will fail?

When enabling both “Force Encryption” and “Force Protocol Encryption” on the Server:

  • What will this yield? Does it matter what Force Encryption is set to when Force Protocol Encryption is enabled?

Answer :

Microsoft’s MSDN blog has a table describing the possible conditions and their outcomes.

See Selectively using secure connection to SQL Server to understand client side setting and connection property options impacting secure connections for just the client involved. The server and other clients are not impacted.

Leave a Reply

Your email address will not be published. Required fields are marked *