Can a user retrieve the connection certificate for an SQL Server instance (similar to how someone can retrieve an HTTP certificate)? If so, how?
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
- the client must not request encryption, and the server must set
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.