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?

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.

| 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.