See Using Encryption Without Validation . Aside from the connection setting 'Trust Server Certificate', there is another setting, the client setting 'Trust Server Certificate', which is configured from the Configuration Manager. If any of the two is set, a self-signed certificate will succeed (actually is a bit more complex as the client setting No will override the connection setting... see the link).

SQL Server 2016 versions ship with TLS 1.0 to TLS 1.2 support ( Transport Layer Security ).

TLS 1.0, 1.1 and SSL 3.0 should be disable, and use only TLS 1.2 for client-server communication.

(other versions of SQL servers is supported with TLS 1.2 by CU cumulative update )

SQL server use a protocol named TDS as an application layer over TCP. No known vulnerabilities have been reported for the Microsoft TDS implementation.

Because several standards-enforcement organizations are mandating the use of TLS 1.2 for encrypted communication channels, Microsoft is releasing the support for TLS 1.2 for the widespread SQL Server installation base.

review: TLS 1.2 support for Microsoft SQL Server

Is SQL Server Prevent MITM attacks (Man In The Middle)

The certificate authority system is designed to stop the man-in-the-middle attacks. In TLS, the SQL server uses the private key associated with their certificate to establish a valid connection and encrypt all traffic over wire. The server keeps the key secret, so the man-in-the-middle can’t use the SQL server real certificate;

Using of self signed certificates :

The use of self signed certificates is never acceptable in production environment but may be used in test environment. Use SHA256 signed certificate/chain, SHA1 is becoming obsolete.

You can apply the rules related to Server certification described in Transport Layer Protection Cheat Sheet

Windows administrators can enable TLS 1.2 support for Windows O.S to get benefit of TLS 1.2

For your point:

SQL Server by default does nothing to prevent man-in-the-middle attacks when configuring your SQL Server to enforce connection encryption.

Security is a policy managed by a security policy written document and implemented by Administrators of O.S/Database/Network/Application... and audited.

Is SQL server compliant to the Security standard? The Answer is yes. Recently ALL old SQL servers 2008/2012 is becoming TLS 1.2 support with CU.

Conclusion:

• Organizations should avoid using self assigned Certification in production Environment and use CA certifications.
• DBA /Windows/Network Admin can implement TLS 1.2 for SQL server/Windows and network infra structure.
• Connection String in application can support Encryption.

I believe this was due to a bug in the ODBC driver, which is now fixed with a big breaking change in the ODBC 18 driver see ODBC 18 Driver release article . The Trust option was ignored if Encrypt=no was specified in the client connection string.