7

Can a user retrieve the connection certificate for an SQL Server instance (similar to how someone can retrieve an HTTP certificate)? If so, how?

Context

If encryption is requested by the SQL Server client, the server certificate must be validated before allowing an encrypted connection. Development servers are often configured with self-signed certificates, which won't validate under default client setup.

To use a self-signed certificate to encrypt a connection, either the certificate must be added to the local store, the client must set "TrustServerCertificate=true", or the client mustn't request encryption and the server must set "ForceEncryption" to "yes". The latter two options are open to MitM attacks every time a connection is made. For the 2nd, some clients don't save the connection setting, so it must be re-set for each new connection. Thus the most desirable solution is to add the certificate to the local store (it takes a little more work initially, but after that is easiest & safest option).

For users in a domain, the certificate is easy enough to distribute, but otherwise the certificate will need to be installed manually on each computer. An admin could provide the certificate to each user, either by sending directly or making it available as a network resource, but sometimes the admin may be too busy or unresponsive for other reasons to provide the certificate. In such a case, it would be useful if the user could get the certificate from the SQL server directly, just as a certificate can be retrieved from an HTTP server using a browser or openssl s_client. This would still be vulnerable to a MitM attack when the certificate was retrieved, but provides a much narrower window than trusting the certificate every time there's a new connection.

1

Technically it is possible, both TDS and TLS/SSL are documented. But you are asking the wrong question.

There is no point in willy-nilly manually installing a self-signed certificate everywhere, it buys you nothing. You will only install the MitM's cert everywhere...

Focus on deploying a properly configured PKI infrastructure. Either use a trusted certificate (ie. Verisign et all), or install a PKI trust in your network and use a infrastructure signed certificate.

See this series: Designing and Implementing a PKI

  • 5
    I disagree -- there is one very good purpose to manually getting a certificate of a SQL server, which is to look at it. In my case, I want to see what domain(s) it contains, and have no easy way (that I know of) to just grab the cert from the server. All major browsers give you this easy and useful tool; if it was not useful, browsers would not have it either. – Abacus Dec 8 '16 at 18:02

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.