SQL Server SSL Encryption - SelfSign Cert working - why?

Question

I realize this is strange behaviour and I wonder if it is supposed to be working that way when using SQL Server generated self-signed certificate.

1. Client (Management Studio) with "encrypt connection" option set, client will verify self-signed cert on SQL Server side and fail because the self-sign cert is not trusted

2. Client (Management Studio) without 'encrypt connection' option set but "Force Encryption" is turned on at SQL Server side using SQL Server Configuration Manager. Client will not verify the self-signed cert and will connect successfully with the connection encrypted.

Questions

1. Why doesn't the client attempt to verify the SQL Server self-signed cert when encryption is enforced on Server Side? Why does the client only verify the self-signed cert when encryption is enforced on the client side?

2. If cert authentication is not done on client side for server-side encryption, what's the point of using a CA signed cert?

To be more precise: Why doesn't the client (Mgmt Studio) attempt to verify/authenticate the cert (be it self-signed/CA signed) when only server-side encryption is turn on ? Why does the client only authenticate/verify the cert given by the server when client-side encryption ( "encrypt connection" ) is turned on?

I added a signed cert (signed by an internal CA) to be used for server-side encryption. This requires the internal CA cert to be installed (into trusted root CA) on the client machine, but I did not do that. However, the client (mgmt studio) still connects successfully when server-side encryption is turned on.

Answer 1

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.

---

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.

---

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