11

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?
Improve this question
1
  • To be clear, setting "Force Protocol Encryption" on the SQL Server's server won't affect clients that aren't on that exact server - it will only affect clients running on that server when they connect to itself - or to other SQL Server instances elsewhere. So, generally, setting "Force Protocol Encryption" on the SQL Server itself won't do anything (as you don't usually run apps on your SQL Server itself). In summary, "Force Encryption" changes the SQL Server itself (to force encryption), "Force Protocol Encryption" affects how clients themselves connect to SQL Server.
    – csrowell
    May 11, 2021 at 13:35

1 Answer 1

Reset to default
6

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
1
  • Unfortunately that blog post only talks about client options. By default if the client requests encryption for data, the server certificate must be valid (i.e. not self-signed). But when the server requires encryption, the clients (SQLOLEDB, SQLNCLI, SQLNCLI10, SQLNCLI11, MSOLEDBSQL, but not MSOLEDBSQL19) will by default ignore certificate errors.
    – Ian Boyd
    Oct 21, 2022 at 15:57

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.