Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for MSSQLSERVER-->Properties-->Flags-->Force Encryption). If you don't install a certificate from a trusted CA, SQL Server will use a self-signed certificate.
Clients cannot validate self-signed certificates because no trusted third party exists. Consequently, a client that requires encryption (Encrypt=true
) must also explicitly specify that the certificate is to be trusted for the for the connection to succeed with a self-signed cert. TrustServerCertificate=true
is basically saying "yes, I understand the risks but connect anyway" and the certificate chain isn't validated.
A CA-issued certificate adds a trusted third party into the mix so the cert chain can be validated by the client with TrustServerCertificate=false
. Although you could connect with TrustServerCertificate=true
and a CA-issued cert (which I think is your second question), this is less secure because a self-signed cert could be used unwittingly by the client.
EDIT:
Why doesn't the client (Mgmt Studio) attempt to verify/authenticate the Cert (be it self-signed/CA signed) when only ServerSide encryption is turn on ?
Why does the client only authenticate/verify the Cert given by the Server when > client-side encryption ( "encrypt connection" ) is turn on ?
The server can force encrypted traffic for all clients but the onus is always on the client to verify the certificate. Consider the default connection setting is Encrypt=false
in SSMS and other clients. This is saying "I don't need encrypted traffic so connect regardless of server settings or certificate." The ForceEncryption=true
server setting strengthens this default by encrypting traffic regardless of the client's requested encryption level. This behavior aligns with this excerpt from the connection string syntax documentation for ADO.NET providers:
Application settings never reduce the level of security set by SQL Server Client Configuration Manager, but may strengthen it.
Since the client didn't request encryption in the first place, certificate verification isn't performed but security is still strengthened by encrypting traffic with whatever certificate is provisioned on the SQL Server. The client can specify Encrypt=true
and TrustServerCertificate=false
to strengthen security further. This not only verifies a trusted certificate exists but also encrypts traffic even if ForceEncryption=false
on the server.
I hope this makes things clearerClients have to opt-in to certificate verification. This is done to enable you to force encryption from the server without getting a trusted cert, installing a cert on the client or changing the client connection strings. See Using Encryption Without Validation in the product documentation.