It has come to my surprise that SQL Server by default does nothing to prevent man-in-the-middle attacks when configuring your SQL Server to enforce connection encryption.
Forcing encryption on your connections is dead-simple. You go to Sql Server Configuration Manager, expand SQL Server Network Configuration, Protocols for [your instance], right-click TCP/IP, change Force Encryption to 'Yes', restart SQL Server and you're done.
Per Microsoft TechNet (https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx), this will generate a Self-Signed Certificate. Fine, no problem, But I expect my clients to complain that the certificate is not trusted. However, they do not. They happily connect to it. I then realize that 'Trust server certificate' is checked by default in SQL Management Studio. Fine, but the most shocking realization is: even if you uncheck "Trust server certificate", SSMS does not complain, warn, or reject the connection.
So by default SQL Server is completely susceptible to MITM attacks because clients do nothing to verify the certificate from SQL Server. The certificate shouldn't have to be signed by a CA: the certificate should have to be configured as a trusted certificate on the client. If that's not true, what is the "Trust server certificate" checkbox for?
Best Answer
When I saw this question, I opened SSMS with the intent to read the help documentation on the "Trust server certificate" option only to discover that this is an undocumented feature with no description of what it does on MSDN. My guess is that it can be used to trust expired certificates or those which can't be validated by walking the chain of trust. Since there is no chain of trust for a self signed certificate, SSMS trusts it when it verifies the signature using the public key.
I think your suggestion would make an excellent server configuration option for high security implementations of SQL Server. It seems like you want a configuration option to reject all self-signed certificates, since avoiding those is the only real way to mitigate a man in the middle attack. It is exactly the concern that you raise that causes me to recommend configuring the Certificate tab with a domain or public CA certificate.