1

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. Force Encryption set to Yes

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.

Trust server certificate is unchecked

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?

1

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.

  • Connection string options are documented here. I understand the dialog is not properly documented – Remus Rusanu Aug 23 '16 at 21:13
  • 2
    btw, no one will look at comments on MSDN pages. If you want an action to happen you should open a connect – Remus Rusanu Aug 23 '16 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.